?
Solved

sql query to convert between tz

Posted on 2014-02-26
4
Medium Priority
?
2,244 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 74

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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 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 74

Accepted Solution

by:
sdstuber earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

750 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