Get Current Time in a Different Time Zone

Your rating: None Average: 5 (2 votes)

It is relatively easy to select current_timestamp from dual and get current date time in your time zone. But things get a little more complicated when you want to get date and time in a different time zone. Here is how you can accomplish that:

-- Get current date and time as well as current date and time in a different time zone (using eastern time zone).
SELECT CURRENT_TIMESTAMP currt_dt,
 CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
  FROM DUAL;

CURRT_DT                            DIFFERENT_DT                      
----------------------------------- -----------------------------------
21-JUL-08 09.13.04.431522 AM -05:00 21-JUL-08 10.13.04.431522 AM -04:00

1 row selected.

-- Same as above but with no time zone display (-05:00)
SELECT LOCALTIMESTAMP currt_dt, LOCALTIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
  FROM DUAL;

CURRT_DT                        DIFFERENT_DT                      
------------------------------- -----------------------------------
21-JUL-08 09.15.37.211514 AM    21-JUL-08 10.15.37.211514 AM -04:00

1 row selected.

And here is another one with better formatted time stamp.
SELECT TO_CHAR ((LOCALTIMESTAMP AT TIME ZONE TZ_OFFSET ('EST')),
                'MM/DD/YYYY HH:MI:SS') different_zone_dt
  FROM DUAL;

DIFFERENT_ZONE_DT  
-------------------
07/21/2008 10:17:47

1 row selected.

A complete list of available time zones can be found by selecting from v$timezone_names table.
SELECT DISTINCT tzname
           FROM v$timezone_names;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Nitin's picture
User offline. Last seen 1 year 46 weeks ago. Offline
Joined: 06/30/2008
Posts: 73
Re: Get Current Time in a Different Time Zone

If want to to same thing in PeopleSoft , this can help

DateTimeToTimeZone

DateTimeToTimeZone(OldDateTime, SourceTimeZone, DestinationTimeZone)

&NEWDATETIME = DateTimeToTimeZone(TESTDTTM, "PST", "EST");

Lepa's picture
User offline. Last seen 2 days 13 hours ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Get Current Time in a Different Time Zone

Thanks Nitin, why not submit that as a Code Sample to your blog?! :)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
7 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.