CODEWITHSUNDEEP
sqloracle-databasetimezone
sworded
sworded

Reputation: 2499

How to run an Oracle Query for another timezone

I have an Oracle database set up in CST -6 and my dates are stored in that time zone. I'd like to run a single query and return any dates in a different time zone, PST -8. Is this possible?

select start_date, end_date from user_record;

Current: 2015-12-01 03:34:46    2015-12-01 04:23:10
Expected: 2015-12-01 01:34:46   2015-12-01 02:23:10

Upvotes: 1

Views: 229

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Try this one:

select 
    start_date AT TIME ZONE '-08:00', 
    end_date AT TIME ZONE '-08:00'
from user_record;

Upvotes: 0

Preethi Raju
Preethi Raju

Reputation: 136

New_time function converts a date and time from one time zone to another in oracle.

SELECT NEW_TIME (TO_DATE ('2015-12-01 03:34:46', 'YYYY-MM-DD HH:MI:SS'),
                 'CST',
                 'PST'
                )
FROM DUAL;

Output: 12/1/2015 1:34:46 AM

Hope it helps

Upvotes: 1

Related Questions