Solved

sql query to convert between tz

Posted on 2014-02-26
4
1,914 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
ID: 39889246
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
ID: 39889255
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
ID: 39889299
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
ID: 39895311
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

777 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