Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mahesh.

Asked: October 02, 2016 - 3:19 pm UTC

Last updated: October 06, 2016 - 12:57 am UTC

Version: oracle 119 EE

Viewed 1000+ times

You Asked

i want to get date as
UTC_TO_UK_TIME INTERVAL DAY(2) TO SECOND(6),
UTC_TO_EASTERN_TIME INTERVAL DAY(2) TO SECOND(6),
UTC_TO_CENTRAL_TIME INTERVAL DAY(2) TO SECOND(6),
UTC_TO_MOUNTAIN_TIME INTERVAL DAY(2) TO SECOND(6),
UTC_TO_PACIFIC_TIME INTERVAL DAY(2) TO SECOND(6),
UTC_TO_INDIAN_TIME INTERVAL DAY(2) TO SECOND(6),

and Connor said...

The INTERVAL data types are not dates, they are intervals, (ie, *durations* of time). If you want to convert date between timezones, check out the timezone functions in the SQL language guide

http://docs.oracle.com/database/121/SQLRF/toc.htm

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sergiusz Wolicki, October 05, 2016 - 9:26 pm UTC

If the question is about knowing the difference between two time zones in a form of a date-to-seconds interval, then the expression to use would be 

to_dsinterval(substr(tz_offset('<tz>'),1,1)||'0 '||
              substr(tz_offset('<tz>'),2,5)||':00')


where <tz> is 'Europe/London', 'US/Eastern', 'US/Central', 'US/Mountain', 'US/Pacific', or 'Asia/Kolkata', correspondingly.

Thanks,
Sergiusz
Connor McDonald
October 06, 2016 - 12:57 am UTC

nice input.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.