Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle daylight savings

Posted on 2014-10-22
3
Medium Priority
?
419 Views
Last Modified: 2014-10-22
I am trying to conver the following epoch 1413902247 (2:37 pm on 10/21 in GMT therefre 10:37 am EST) to a date and time. My env is EST and I believe DST is in effect and we are -4 from GMT as of today (10/22/2014).
I have been tring various things below and can get the time. I cannot seem to get it converted to EST with daylight when I use EDT i get invalid timezone?


 SELECT CAST ((from_tz(CAST(to_Date('1970-01-01','YYYY-MM-DD') +1413902247/86400 AS TIMESTAMP),'EST') at TIME ZONE ('US/Eastern')) as DATE) --as EST
,from_tz(cast(to_Date('1970-01-01','YYYY-MM-DD') +1413902247/86400 AS TIMESTAMP),'US/Eastern') as EDT
       ,to_Date('1970-01-01','YYYY-MM-DD') +1413902247/86400 AS TIMESTAMP
--,                          to_char(new_time(to_date('1970-01-01','YYYY-MM-DD HH24:MI:SS') +1413902247/86400000) ,'EST','GMT'),'dd-mon-yyyy hh24:mi:ss') New_TimeGmtTime_DST
   FROM dual
0
Comment
Question by:pcomb
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40397449
SELECT (TIMESTAMP '1970-01-01 00:00:00 +00:00' + NUMTODSINTERVAL(1413902247, 'second'))
           AT TIME ZONE 'US/Eastern'
  FROM DUAL


US/Eastern is daylight savings aware, so it will return -04:00 in the DST period  and -05:00 in the Standard period
0
 

Author Comment

by:pcomb
ID: 40397459
perfect thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40397474
and, if you need to reverse it, then try one of these


SELECT   EXTRACT(DAY FROM i) * 86400
       + EXTRACT(HOUR FROM i) * 3600
       + EXTRACT(MINUTE FROM i) * 60
       + EXTRACT(SECOND FROM i)
  FROM (SELECT TIMESTAMP '2014-10-21 10:37:27 -04:00' - TIMESTAMP '1970-01-01 00:00:00 +00:00' i
          FROM DUAL);

SELECT   EXTRACT(DAY FROM i) * 86400
       + EXTRACT(HOUR FROM i) * 3600
       + EXTRACT(MINUTE FROM i) * 60
       + EXTRACT(SECOND FROM i)
  FROM (SELECT   FROM_TZ(TIMESTAMP '2014-10-21 10:37:27', 'US/Eastern')
               - TIMESTAMP '1970-01-01 00:00:00 +00:00'
                   i
          FROM DUAL);
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

782 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