CODEWITHSUNDEEP
oracle-databasetimezonetimestamp
neha dhawan
neha dhawan

Reputation: 13

Oracle timezone convert UTC to PST

I have a requirement to check tasks in progress using Scheduled start time (PST) and scheduled end time(PST) .Datatype is timestamp with timezone for Scheduled start time and scheduled end time .

sysdate ( UTC )should be in between the start and end date to confirm tasks in progress

JIRA_KEY  sch_start_time               sch_end_time

1         11/04/2016 1:00:00 AM PST   14/04/2016 2:00:00 PM PST

2         12/04/2016 2:00:00 AM PST   12/04/2016 5:00:00 AM PST

3         12/04/2016 2:00:00 AM PST   14/04/2016 5:00:00 AM PST

Expected Output -

JIRA_KEY  sch_start_time               sch_end_time

1         11/APR/2016 1:00:00 AM PST   14/APR/2016 2:00:00 PM PST

3         12/APR/2016 2:00:00 AM PST   14/APR/2016 5:00:00 AM PST

SYSDATE is in UTC and as of now is 13/APR/2016 8:15 AM

I tried this. But not sure abt the conversion using to_timestamp_tz

select JIRA_KEY,To_CHAR( SCH_END_TIME,'DD/MON/YY HH:MI:SS PM') as SCH_END_TIME
 FROM HW_JIRA_STATUS WHERE  
to_timestamp_tz(SYSDATE,'DD/MON/YY HH:MI:SS PM') between SCH_START_TIME and SCH_END_TIME

Upvotes: 1

Views: 7278

Answers (2)

Alex Poole
Alex Poole

Reputation: 191445

When you do

to_timestamp_tz(SYSDATE,'DD/MON/YY HH:MI:SS PM')

you're implicitly converting SYSDATE to a string using your NLS_DATE_FORMAT, and then converting that string to a timestamp with time zone - but you aren't specifying the time zone, so it will use the database time zone by default (which is what you want anyway).

Relying on NLS settings is dangerous, so you should at least use an expliclt format mask:

to_timestamp_tz(to_char(SYSDATE, 'DD/MON/YY HH:MI:SS PM'),'DD/MON/YY HH:MI:SS PM')

... but you can also cast between data types without converting to intermediate strings; e.g. to again use the DB server time zone:

cast(SYSDATE as timestamp with time zone)

You don't need to do any conversion yourself though; you can use SYSTIMESTAMP instead:

WHERE SYSTIMESTAMP between SCH_START_TIME and SCH_END_TIME;

That is already in the DB server's time zone.

Upvotes: 1

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

Try this query 

select JIRA_KEY,To_CHAR( SCH_END_TIME,'DD/MON/YY HH:MI:SS PM') as SCH_END_TIME
FROM HW_JIRA_STATUS 
WHERE systimestamp AT TIME ZONE 'PST' between SCH_START_TIME and SCH_END_TIME

Upvotes: 0

Related Questions