7 Replies Latest reply: Dec 13, 2012 3:06 AM by Mr Lonely RSS

    Any date(EST or EDT) to UTC

    Mr Lonely
      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
          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
            Why do you want to re-invent the Wheel?
            • 3. Re: Any date(EST or EDT) to UTC
              Mr Lonely
              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-Oracle
                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
                  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
                    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
                      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.