2 Replies Latest reply: Jun 24, 2011 1:43 PM by MichaelS RSS

    Convert timestamp to UTC

    871170
      I am storing date&time in one column and Timezone in one column. So I need to convert this to UTC format.

      Note: I will be having different timezones in timezone column.

      We have a function to convert the timestamp to UTC format . i.e. SYS_EXTRACT_UTC

      Example is:
      SELECT SYSTIMESTAMP, SYS_EXTRACT_UTC (SYSTIMESTAMP) FROM DUAL

      SELECT SYS_EXTRACT_UTC(TIMESTAMP '2005-05-13 19:15:26 PST') FROM dual

      when I execute the below query it is giving error like : TIMEZone region is not found

      SELECT SYS_EXTRACT_UTC(TIMESTAMP '2005-05-13 19:15:26 IST') FROM dual

      And I used the NEW_TIME also like the below

      select new_time(t.start_date, 'PDT', 'GMT') from test t -- This is working fine.

      select new_time(t.start_date, 'IST', 'GMT') from test t -- This is not working. because NEW_TIME supporting limited timezones.

      Can any body help me out to convert all timezones Timestamps to UTC/GMT.

      Can anybody suggest me is there any oracle function to support all timezones and convert the timestamps to UTC/GMT?

      Please mail me to sivakumari17@gmail.com
        • 1. Re: Convert timestamp to UTC
          Richard Smith
          To see what timezones are currently supported:
          select * from v$timezone_names;

          To convert dates or timestamps between time zones use:

          CREATE TABLE x(
          dt DATE,
          tz VARCHAR2(30));

          INSERT INTO x
          VALUES (SYSDATE, 'US/Central');

          INSERT INTO x
          VALUES (SYSDATE, 'Europe/Berlin');

          INSERT INTO x
          VALUES (SYSDATE, 'Asia/Tokyo');

          COMMIT;

          SELECT dt, tz, FROM_TZ(CAST(dt AS TIMESTAMP), tz) AT TIME ZONE 'UTC' utc_dt FROM x;

          CREATE TABLE y(
          ts TIMESTAMP,
          tz VARCHAR2(30));

          INSERT INTO y
          VALUES (SYSDATE, 'US/Central');

          INSERT INTO y
          VALUES (SYSDATE, 'Europe/Berlin');

          INSERT INTO y
          VALUES (SYSDATE, 'Asia/Tokyo');

          COMMIT;

          SELECT ts, tz, FROM_TZ(ts, tz) AT TIME ZONE 'UTC' utc_dt FROM y;

          CREATE TABLE z (tstz TIMESTAMP WITH TIME ZONE);

          INSERT INTO z
          VALUES (SYSTIMESTAMP);

          COMMIT;

          SELECT tstz, DBTIMEZONE, SESSIONTIMEZONE FROM z;

          ALTER SESSION SET time_zone='Asia/Tokyo';

          SELECT tstz, DBTIMEZONE, SESSIONTIMEZONE FROM z;

          INSERT INTO z
          VALUES (CURRENT_TIMESTAMP);

          COMMIT;

          SELECT tstz,
          DBTIMEZONE,
          SYSTIMESTAMP,
          SESSIONTIMEZONE,
          CURRENT_TIMESTAMP
          FROM z;
          • 2. Re: Convert timestamp to UTC
            MichaelS
            Can anybody suggest me is there any oracle function to support all timezones and convert the timestamps to UTC/GMT?
            How about
            SQL> with t as (
              select sysdate dt, 'IST' tz from dual
            )
            --
            --
            select sys_extract_utc (
                     to_timestamp_tz (to_char (dt, 'rrrr-mm-dd hh24:mi:ss') || ' ' || tzname, 'rrrr-mm-dd hh24:mi:ss tzr')) x
              from t, v$timezone_names
             where tzabbrev = tz
               and rownum = 1
            /
            X                              
            -------------------------------
            24-JUN-11 03.13.23.000000000 PM
            1 row selected.
            ?