sql query to convert between tz

sam_2012
sam_2012 used Ask the Experts™
on
Hi ,

I need to write an function which will convert a given date from gmt to est and est to gmt timezone. I have to take care of daylight savings also.

Iam using new_time as below

v_est:= new_time(sysdate,'gmt','est')
v_gmt:= new_time(sysdate,'est'.'gmt'). But this function does not handle day light saving.

My function needs to handle the below condition,

I have to check whether daylight saving needs to be applied based on the diff between dates in various timezone and then apply it while converting it from gmt to est and est to gmt.
else
do not apply daylight saving, directly converting between gmt and est and est and gmt.
end if;

any help is really appraciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
If you're going between EST and GMT  then you don't have to worry about daylight saving time because neither of those time zones observe DST transitions
Most Valuable Expert 2011
Top Expert 2012
Commented:
If, you mean you want to go between Eastern PREVAILING time  (EPT) and GMT then check out the sds_newtime function found here...

http://www.experts-exchange.com/Database/Oracle/Q_23526953.html

from GMT to EPT:
sds_newtime(d,'GMT','EPT')

from EPT to GMT:
sds_newtime(d,'EPT','GMT')

where "d" is whatever DATE value you're trying to convert.


Note EPT is not a real time zone,  it's just a common representation for saying Eastern Standard or Daylight Saving Time without having to change names in the year


Also note, sds_newtime supports an optional 4th parameter that lets you determine how you want to handle the fall transition when the 1am-2am hour repeats (once in EDT then again in EST)
Most Valuable Expert 2011
Top Expert 2012
Commented:
alternately, you could use all built-in  functions by  converting your date values to timestamps with time zone (which will recognize dst changes) and then use time zone conversions

GMT to EPT:
CAST(FROM_TZ(CAST(d AS TIMESTAMP), 'GMT') AT TIME ZONE 'US/Eastern' AS DATE)

EPT to GMT:
CAST(FROM_TZ(CAST(d AS TIMESTAMP), 'US/Eastern') AT TIME ZONE 'GMT' AS DATE)

where "d" is whatever DATE value you're trying to convert


For the fall transition, the timestamp conversions will always assume the second hour (standard time)

Author

Commented:
awesome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial