This discussion is archived
7 Replies Latest reply: Dec 13, 2012 1:06 AM by Mr Lonely RSS

Any date(EST or EDT) to UTC

Mr Lonely Newbie
Currently Being Moderated
Hi ,

I am trying to create a function which will convert any date ( EST and EDT will be taken care automatically) to UTC. Can any one help with the logic / steps?

Thanks and regards,
Lonely
  • 1. Re: Any date(EST or EDT) to UTC
    Ashu_Neo Pro
    Currently Being Moderated
    You can go through a link in otn itself.
    Link:- Convert local time to utc time

    Thanks!
  • 2. Re: Any date(EST or EDT) to UTC
    jeneesh Guru
    Currently Being Moderated
    Why do you want to re-invent the Wheel?
  • 3. Re: Any date(EST or EDT) to UTC
    Mr Lonely Newbie
    Currently Being Moderated
    ok .. I was not aware of it ...

    I think I need to pass timezone along with the date with this. Lik
    select sessiontimezone into tz from dual;
    stmt := 'select sys_extract_utc (timestamp ' || '''' || to_char(local_time, 'YYYY-MM-DD HH24:MI:SS') || ' ' || tz || ''') from dual '; 
    execute immediate stmt into utc_time;
    But now with eastern time we have the problem with daylight saving time. So how will I decide when I need to send timezone as EST and when EDT?

    And it's not that I am passing the sysdate. The date can be of anydate. So I need to check whether that day is in EST or EDT.

    So how to do that?
  • 4. Re: Any date(EST or EDT) to UTC
    Nicosa Expert
    Currently Being Moderated
    Hi,
    Mr Lonely wrote:
    But now with eastern time we have the problem with daylight saving time. So how will I decide when I need to send timezone as EST and when EDT?

    And it's not that I am passing the sysdate. The date can be of anydate. So I need to check whether that day is in EST or EDT.
    The database knows by itself when a date is EST/EDT (or to be more precise : The database knows that April 12 cannot be EDT)

    In your case I would just use FROM_TZ function, and use AT TIME ZONE 'UTC', such as :
    [11.2] Scott @ My11g > ed
    Wrote file afiedt.buf
    
      1  with t (mytmstmp,mytz) as (
      2       select to_timestamp('20120402103000.123456','yyyymmddhh24missxff'), 'EST' from dual
      3       union all select to_timestamp('20120402103000.123456','yyyymmddhh24missxff'), 'PST' from dual
      4       union all select to_timestamp('20120917173000.123456','yyyymmddhh24missxff'), 'EST' from dual
      5       union all select to_timestamp('20120917173000.123456','yyyymmddhh24missxff'), 'PST' from dual
      6  )
      7  ------ end of sample data ------
      8  select
      9       mytmstmp
     10       ,mytz
     11       ,from_tz(mytmstmp,mytz) at time zone 'UTC' at_utc
     12* from t
    [11.2] Scott @ My11g > /
    
    MYTMSTMP                                 MYTZ   AT_UTC
    ---------------------------------------- ------ --------------------------------------------------
    02-APR-12 10.30.00.123456000 AM          EST    02-APR-12 03.30.00.123456000 PM UTC
    02-APR-12 10.30.00.123456000 AM          PST    02-APR-12 05.30.00.123456000 PM UTC
    17-SEP-12 05.30.00.123456000 PM          EST    17-SEP-12 10.30.00.123456000 PM UTC
    17-SEP-12 05.30.00.123456000 PM          PST    18-SEP-12 12.30.00.123456000 AM UTC
    But I don't know how would be handled timestamps that could be both EST or EDT...
  • 5. Re: Any date(EST or EDT) to UTC
    Mr Lonely Newbie
    Currently Being Moderated
    ok.

    I did some experiment and came up with this function. I know may be the function is not needed but I need it so simplify my query.
    CREATE OR REPLACE FUNCTION fn_eastern_to_utc (in_date DATE)
       RETURN DATE
    AS
    BEGIN
       IF in_date IS NOT NULL
       THEN
          RETURN CAST (FROM_TZ (CAST (in_date AS TIMESTAMP), 'US/Eastern') AT TIME ZONE 'GMT' AS DATE);
       ELSE
          RETURN NULL;
       END IF;
    END;
    /
    Edited by: Mr Lonely on Dec 13, 2012 1:57 PM
  • 6. Re: Any date(EST or EDT) to UTC
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    considering that EST and EDT are always:

    EST=UTC−5:00
    EDT=UTC−4:00

    Maybe I'm making too simple but you can to like this:

    If your input data is EST:
    WITH mydates AS
    (
       SELECT SYSDATE + LEVEL/24 dt
         FROM DUAL
      CONNECT BY LEVEL <= 5
      UNION
      SELECT NULL FROM DUAL
    )
    SELECT ROWNUM
         , TO_CHAR(dt,'DD/MM/YYYY HH24:MI:SS') dt
         , TO_CHAR(dt+5/24,'DD/MM/YYYY HH24:MI:SS') dtgmt
      FROM mydates;
    
        ROWNUM DT                  DTGMT              
    ---------- ------------------- -------------------
             1 13/12/2012 10:57:07 13/12/2012 15:57:07
             2 13/12/2012 11:57:07 13/12/2012 16:57:07
             3 13/12/2012 12:57:07 13/12/2012 17:57:07
             4 13/12/2012 13:57:07 13/12/2012 18:57:07
             5 13/12/2012 14:57:07 13/12/2012 19:57:07
             6                                        
    If your input date is EDT change to:
         , TO_CHAR(dt+4/24,'DD/MM/YYYY HH24:MI:SS') dtgmt
    
        ROWNUM DT                  DTGMT              
    ---------- ------------------- -------------------
             1 13/12/2012 10:57:25 13/12/2012 14:57:25
             2 13/12/2012 11:57:25 13/12/2012 15:57:25
             3 13/12/2012 12:57:25 13/12/2012 16:57:25
             4 13/12/2012 13:57:25 13/12/2012 17:57:25
             5 13/12/2012 14:57:25 13/12/2012 18:57:25
             6                                        
    Regards.
    Al
  • 7. Re: Any date(EST or EDT) to UTC
    Mr Lonely Newbie
    Currently Being Moderated
    Hi Alberto,

    I already posted the solution in case someone needs it in future.

    In your solution you need to know when we need to add 4/24 and when 5/24. That's one more headache. How I will know that info for a random date?

    Anyway the simple solution was when you put timezone as US/Eastern then you don't need to bother about EST/EDT. System by default does that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points