sql query to convert between tz

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.
sam_2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
0
sdstuberCommented:
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)
0
sdstuberCommented:
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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
awesome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.