Solved

sql query to convert between tz

Posted on 2014-02-26
4
1,823 Views
Last Modified: 2014-02-28
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.
0
Comment
Question by:sam_2012
  • 3
4 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:sam_2012
Comment Utility
awesome
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now