Get Current Time in a Different Time Zone

Mon, 07/21/2008 - 9:21am — Lepa

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;

Bookmark/Search this post with:

Lepa's blog

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.

Fri, 07/25/2008 - 2:10am

Nitin

Offline

Joined: 06/30/2008

Posts: 53

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");

Fri, 07/25/2008 - 6:49am

Lepa

Offline

Joined: 06/23/2008

Posts: 457

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

Your name: *

E-mail: *

The content of this field is kept private and will not be shown publicly.

Homepage:

Subject:

Comment: *

Input format

Filtered HTML

Allowed HTML tags: <div> <pre> <br> <code> <a> <em> <blockquote> <strong> <ul> <ol> <li> <dl> <dt> <b> <p> <h1> <h2> <u> <img>
Web page addresses and e-mail addresses turn into links automatically.
Lines and paragraphs break automatically.
You can enable syntax highlighting of source code with the following tags: <code>.

BBcode

You can use BBCode tags in the text.
Lines and paragraphs break automatically.
Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

Notify me when new comments are posted

CAPTCHA

The question below is to prevent automated spam submissions.

Math question: * 2 + 15 =

Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Unanswered Forum Posts

Title	Under	Posted on
Abs Mgmt: How can Abs rule pro-rate contractor's last month's entitlement (need help urgently)	PeopleSoft Functional	03/10/2010 - 9:21pm
Can we user LONG datatype as a search option in PeopleSoft	PeopleSoft Technical	03/10/2010 - 11:22am
special caracters are not translated correctly when ftp from windows to unix	Programming	03/09/2010 - 3:23pm
How to view the employees from particular country in peoplesoft?	PeopleSoft Technical	03/03/2010 - 1:56am

Main Menu

User login

Quick links

Sponsored links

Recent blog posts

Who's new