CODEWITHSUNDEEP
sqloracle-database
Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Convert a date/time from UTC to session time zone

My program receives timestamps in UTC, e.g. Tue, 31 May 2016 11:43:47 UTC. How do I convert them to a timestamp in the session's current timezone?

Upvotes: 1

Views: 722

Answers (2)

user5683823
user5683823

Reputation:

First convert the string to a timestamp with timezone (use the correct function). To show the result in your local (session) time zone, use the "AT LOCAL" clause.

select to_timestamp_tz('Tue, 31 May 2016 19:43:47 UTC', 
                                'Dy, dd Mon yyyy hh24:mi:ss tzr') at local from dual;

Result (using my front-end settings for displaying timestamp with timezone - yours may be different):

31-MAY-16 02.43.47.000000000 PM AMERICA/CHICAGO

Upvotes: 2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Use the tz_offset function to get the timezone offset for the session's timezone, then use the from_tz function to convert the string to a timestamp with time zone, and use at time zone to change it to use the session's timezone.

select from_tz(to_timestamp('Tue, 31 May 2016 11:43:47 UTC'
                           ,'Dy, dd Mon yyyy hh24:mi:ss "UTC"')
              , 'UTC')
       at time zone tz_offset(sessiontimezone)
      ,sessiontimezone
from   dual;

31/05/2016 19:43:47.000000000 +08:00    Australia/Perth

Upvotes: 0

Related Questions