Convert timezone to PLSQL

Question

Convert timezone to PLSQL

I need to convert sysdate and time to a specific time zone, such as EST. I can not accept the current time zone.

How to convert this to plsql? Please help me.

+3

sql oracle plsql plsqldeveloper

Suvonkar Apr 26 '12 at 7:21

source share

7 answers

Justin cave · Answer 1 · 2012-04-26T17:37:14+0000

Assuming you have TIMESTAMP WITH TIME ZONE(e.g. systimestamp), you can use the syntax AT TIME ZONE. For example, I can take the current one systimestampand convert it to UTC (GMT), Eastern and Pacific, indicating different names for time zones.

SQL> ed
Wrote file afiedt.buf

  1  select systimestamp at time zone 'UTC' current_time_in_utc,
  2         systimestamp at time zone 'Us/Eastern' current_time_in_est,
  3         systimestamp at time zone 'US/Pacific' current_time_in_pst
  4*   from dual
SQL> /

CURRENT_TIME_IN_UTC
---------------------------------------------------------------------------
CURRENT_TIME_IN_EST
---------------------------------------------------------------------------
CURRENT_TIME_IN_PST
---------------------------------------------------------------------------
26-APR-12 05.36.11.802000 PM UTC
26-APR-12 01.36.11.802000 PM US/EASTERN
26-APR-12 10.36.11.802000 AM US/PACIFIC

cadvena · Answer 2 · 2014-05-16T19:42:58+0000

Oracle already has a timezone table:

SELECT tzname, tzabbrev from V$TIMEZONE_NAMES
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

Get the current time in the EPT for use in the SQL statement:

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Oracle 11g SQL : http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

, , , :

current_timestamp
DBTIMEZONE
localtimestamp
Trun ()
sessiontimezone
sys_extract_utc
systimestamp
to_timestamp
to_timestamp_tz
TZ_OFFSET
ALTER SESSION SET TIME_ZONE = '+00: 00'; - UTC EPT

(sysdate)

 Select sysdate from dual;

select LOCALTIMESTAMP FROM DUAL;

UTC

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL

- sysdate to_date localtimestamp, to_timestamp to_timestamp_tz. sysdate to_date, .

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL; –- returns essentially the same as sysdate but in UTC

or 

ALTER SESSION SET TIME_ZONE = 'UTC';
select LOCALTIMESTAMP FROM DUAL –- after setting session time_zone to ‘UTC’ this will now return a UTC timestamp

UTC SQL-.

-- Get current time in UTC format and subtract 5 minutes.

    LOCALTIMESTAMP at time zone '+00:00' - 5/1440

-- Trunc the time to eliminate seconds

TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI')

-- Convert to characters then back to datetime. 

to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

-- Select from dual to show it works. 

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Get the current time in EPT for use in a SQL statement.

    -- Get current time in UTC format and subtract 5 minutes.
    ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
    select LOCALTIMESTAMP from dual - 5/1440
-- Trunc the time to eliminate seconds

    TRUNC(LOCALTIMESTAMP - 5/1440, 'MI')

    -- Convert to characters then back to datetime. 
    to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

    -- Select from dual to show it works. 
    select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

DB

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET TIME_ZONE = 'UTC'; -- set to UTC time same as command above
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results
ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results

SELECT TO_TIMESTAMP_TZ('05/16/2014 11:26:48 -04:00',
   'MM/DD/YYYY HH:MI:SS TZH:TZM') FROM DUAL;


SELECT tzname, tzabbrev from V$TIMEZONE_NAMES where tzabbrev = 'EPT';
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

-- The following example casts a null column in a UNION operation as TIMESTAMP WITH LOCAL TIME ZONE using the sample tables oe.order_items and oe.orders:

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

(TO_TIMESTAMP_TZ)

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONEdatatype.

: TIMESTAMP WITH TIME ZONE:

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')

UNION TIMESTAMP WITH LOCAL TIME ZONE, tablesoe.order_items oe.orders:

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

/

ALTER SESSION SET TIME_ZONE = '-5:00';
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

(LOCALTIMESTAMP)

LOCALTIMESTAMP TIMESTAMP. CURRENT_TIMESTAMP , LOCALTIMESTAMP TIMESTAMP, CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE.

ALTER SESSION SET TIME_ZONE = '-5:00';
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP:

    INSERT INTO local_test VALUES
   (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

The code above is required to include the TIME ZONE portion of the return type of the function

(CURRENT_TIMESTAMP)

CURRENT_TIMESTAMP TIMESTAMP WITH TIMEZONE. SQL

    ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

Jokke Heikkilä · Answer 3 · 2012-04-27T12:48:00+0000

?

select to_timestamp_tz(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' ' || 'FROM_TIME_ZONE', 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'TO_TIME_ZONE'
from dual;

weenoid · Answer 4 · 2012-04-26T08:28:12+0000

EST (UTC - 5 ) :

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL

, 2 :

,
, .

EST, ; , , .

Bob Jarvis · Answer 5 · 2012-04-26T17:28:38+0000

:

CREATE TABLE TIMEZONES (ZONE          CHAR(1) PRIMARY KEY,
                        NAMES         VARCHAR2(25) NOT NULL,
                        OFFSET_HOURS  NUMBER NOT NULL);

:

INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Z', 'GMT', 0);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('N', '-1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('O', '-2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('P', '-3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Q', '-4 EDT', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('R', 'EST CDT', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('S', 'CST MDT', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('T', 'MST PDT', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('U', 'PST', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('V', '-9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('W', '-10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('X', '-11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Y', '-12', -12);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('A', '1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('B', '2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('C', '3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('D', '4', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('E', '5', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('F', '6', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('G', '7', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('H', '8', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('I', '9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('K', '10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('L', '11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('M', '12', -12);

,

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) + (tz.OFFSET_HOURS / 24)
  FROM TIMEZONES tz
  WHERE tz.NAMES LIKE '%EDT%';

  WHERE tz.ZONE = 'Q'

-4- .

.

user3754470 · Answer 6 · 2014-06-18T23:17:20+0000

. , Oracle tz_offset . , ,

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

'-04: 00.' "-05: 00". , , ( ), .

,

SELECT TZ_OFFSET('EST') FROM DUAL;

'-05: 00.' , (, ) . , . 16 , DST .

SELECT TZ_OFFSET('US/Eastern'), TZ_OFFSET('EST'),  TZ_OFFSET('EST5EDT') FROM DUAL;
SELECT TZ_OFFSET('US/Central'), TZ_OFFSET('CST'),  TZ_OFFSET('CST6CDT') FROM DUAL;
SELECT TZ_OFFSET('US/Mountain'), TZ_OFFSET('MST'),  TZ_OFFSET('MST7MDT') FROM DUAL;
SELECT TZ_OFFSET('US/Pacific'), TZ_OFFSET('PST'),  TZ_OFFSET('PST8PDT') FROM DUAL;

-04:00      -05:00      -04:00
-05:00      -05:00      -05:00
-06:00      -07:00      -06:00
-07:00      -07:00      -07:00

. tz_offset, , . , , , .

Now I can write code to determine if I am in standard or prevailing time. However, I do not see the ability to consistently pull tz_offsetfor standard or daytime, just prevailing.

This leaves the developer with the need to create his own table, as Bob Jarvis did, in order to crack the problem, as it was in the code below.

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

Declare
-- ******** User declarations begin here ******** --




-- ******** User declarations end here ******** --

/*******************  All Declarations of Variables and Functions below this point support Time Zone Conversion (Convert_TZ function) *******************/  
  -- TimeZone Conversion Procedure

  -- User Input (Parameters)
  input_date date := TO_TIMESTAMP_TZ('7/1/2009 18:00','mm/dd/yyyy hh24:mi');  -- Try: LocalTimestamp; or TO_TIMESTAMP('2/1/2009 13:00','mm/dd/yyyy hh24:mi')
  --input_date date := LocalTimestamp;
  input_TZ varchar(3) := 'GMT';  -- Exmaples: EST, EDT or EPT for Eastern Standard, Daylight or Prevailing, respectively.
  output_TZ varchar(3) := 'EPT';  -- 

  -- Variables
  type date_array is table of date;
  return_date date := localtimestamp;
  temp_date date := to_date('10/27/1974 02:00','mm/dd/yyyy hh24:mi');
  type str_array is table of varchar2(10);
  dow_list str_array;

   Function dst_start_stop (input_date DATE) 
        RETURN date_array
    AS
        year_part number(4);
        start_week number(1) := 0;  -- week of month: -1=last, 1=1st, 2=nd, 0=fixed date (like 1974 and 1975)
        stop_week number(1) := 0;   -- week of month: -1=last, 1=1st, 2=nd, 0=fixed date (like 1974 and 1975)
        dst_start date := to_date('01/06/1974 23:59','mm/dd/yyyy hh24:mi');
        dst_stop date := to_date('10/27/1974 23:59','mm/dd/yyyy hh24:mi');
        dst_date date := dst_start;
        dst_msg varchar2(500) := ' ';
        inc_dec number := 0;
        Cnt number(1) := 0;
        dst_dow number(1) := 1;  --  1=Sunday, 2=Monday, etc.
        i number;
        dst_range date_array;
   BEGIN
      dst_range := date_array();
      dst_range.extend(2);
      dst_range(1) := temp_date;
      dst_range(2) := temp_date;
      DBMS_OUTPUT.PUT_LINE('  ** Start: dst_start_stop Func **');
      --insert into dst_range values(dst_start,dst_stop);
      --dst_range(1) := dst_start;
      --dst_range(2) :=  dst_stop;
      year_part := to_number(to_char(input_date,'YYYY'));
      DBMS_OUTPUT.PUT_LINE('      Year: '||year_part);

      -- Determine DST formula based on year of input_date
      If year_part > 9999 Then  --   Invalid TempYear > 9999
          dst_msg := 'N/A.  I can''t guess if DST will be applied after 9999.  Standard Time returned.  ';
          Goto found_start_stop;
      ElsIf year_part >= 2007 Then --   2007 forward.  Latest DST Rules used after 2007.
          dst_msg := '2007 forward: Third National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM second Sunday in March (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  '
          --dst_msg := dst_msg || 'Fall Back 2:00 AM first Sunday in November (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  '
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('03/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          start_week := 2; -- 2nd Sunday in March
          dst_stop := to_date('11/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := 1;  -- 1st Sunday in November
      ElsIf year_part >= 1987 Then  --   1987 thru 2006.
          dst_msg := '1987 thru 2006: Second National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM first Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          start_week := 1; 
          dst_start := to_date('04/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part >= 1976 Then  --   1976 thru 1986 OLD DST Rules used 1961 thru 1973.
          dst_msg := '1976 thru 1986: First National DST Standard (resumed after 1974-1975 extended DST trials).  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM last Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          start_week := -1; 
          dst_start := to_date('04/30/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part = 1975 Then  --   1975 Trial.
          dst_msg := '1975 Trial of Extended DST.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM Feb 23 (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM Oct 26, the last Sun in Oct (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('02/23/1975 02:00','mm/dd/yyyy hh24:mi');
          dst_stop := to_date('10/26/1974 02:00','mm/dd/yyyy hh24:mi');
          Goto found_start_stop;
      ElsIf year_part = 1974 Then  --   1974 Trial.
          dst_msg := '1974 Trial of Extended DST.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM Jan 6 (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00)).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM Oct 27 (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('01/06/1974 02:00','mm/dd/yyyy hh24:mi');
          dst_stop := to_date('10/27/1974 02:00','mm/dd/yyyy hh24:mi');
          Goto found_start_stop;
      ElsIf year_part >= 1961 Then  --   1961 thru 1973 First National DST Standard.
          dst_msg := '1961 thru 1973: First National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM last Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          start_week := -1; 
          dst_start := to_date('04/30/'||input_date||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part >=1900 Then  --   DST was applied inconsistently or not at all
          dst_msg := 'N/A.  Before 1961, DST was applied inconsistently across states or not at all.  Standard Time returned.  ';
          Goto found_start_stop;
      ElsIf year_part < 1900 Then  --   Invalid year_part
          dst_msg := 'N/A. DST never active before 1900';
          Goto found_start_stop;
      Else  --   Invalid year_part 
          dst_msg := 'N/A.  Error.  Invalid datetime value.';
          Goto found_start_stop;
      End If;   
      DBMS_OUTPUT.PUT_LINE('  The code specified the following DST rules for the input date ('||input_date||').  '||dst_msg);
      if start_week > 0 then
        DBMS_OUTPUT.PUT_LINE('    Start on '||dow_list(dst_dow)||' #'||start_week||' of '||trunc(dst_start,'W')||'. ');
      else
        DBMS_OUTPUT.PUT_LINE('    Starts '||start_week||' from the last '||dow_list(dst_dow)||' of '||trunc(dst_start,'W')||'. ');
      end if;
      if stop_week > 0 then
        DBMS_OUTPUT.PUT_LINE('    End on '||dow_list(dst_dow)||' #'||stop_week||' of '||trunc(dst_stop,'W')||'. ');
      else
        DBMS_OUTPUT.PUT_LINE('    Ends '||stop_week||' from the last '||dow_list(dst_dow)||' of '||trunc(dst_stop,'W')||'. ');
      end if;
      DBMS_OUTPUT.PUT_LINE('    ');


      /* Apply formula determined above to find dst start and stop times for the year of the input_date.
            This section is skipped if start/stop already determined or indeterminant. 
      */
        -- DstStartDay 
        inc_dec := start_week/abs(start_week);  -- results in +1 or -1
        Cnt := 0; i:=0;
        while (Cnt < abs(start_week) and i<20) loop
          i:=i+1;
          if (to_char(dst_start,'D') = dst_dow) then 
            Cnt := Cnt + 1; 
            --DBMS_OUTPUT.PUT_LINE('    Found '||dow_list(dst_dow))||' '||Cnt||': '||dst_start)
          end if;
          if (Cnt < abs(start_week)) then 
            dst_start := dst_start + inc_dec; 
          end if;
        end loop;
        case inc_dec
          when 1 then
            DBMS_OUTPUT.PUT_LINE('  Spring forward on  '||dow_list(dst_dow)||' #'||Cnt||' of the month: '||dst_start);
          else
            DBMS_OUTPUT.PUT_LINE('  Spring forward on the last'||dow_list(dst_dow)||'of the month: '||dst_start);
        end case;
      -- DstStopDay 
        inc_dec := stop_week/abs(stop_week);  -- results in +1 or -1
        Cnt := 0; i :=0;
        while (Cnt < abs(stop_week) and i <20) loop -- to_char(dst_stop,'D') > 1 loop
          i:=i+1;
          if (to_char(dst_stop,'D') = dst_dow) then 
            dst_stop := dst_stop + inc_dec; 
            Cnt := Cnt + 1; 
          end if;
          if (Cnt < abs(stop_week)) then 
            dst_stop := dst_stop + inc_dec; 
          end if;
        end loop;
        case inc_dec
          when 1 then
            DBMS_OUTPUT.PUT_LINE('  Fall back on  '||dow_list(dst_dow)||' #'||Cnt||' of the month: '||dst_stop);
          else
            DBMS_OUTPUT.PUT_LINE('  Fall back on the last'||dow_list(dst_dow)||'of the month: '||dst_stop);
        end case;
    <<found_start_stop>>
      dst_range(1) := dst_start; 
      DBMS_OUTPUT.PUT_LINE('      dst_range(1): '||to_char(dst_range(1),'mm/dd/yyyy hh24:mi')||' = '||dst_start);
      dst_range(2) := dst_stop;
      DBMS_OUTPUT.PUT_LINE('      dst_range(2): '||to_char(dst_range(2),'mm/dd/yyyy hh24:mi')||' = '||dst_stop);
      DBMS_OUTPUT.PUT_LINE('  ** Finish: dst_start_stop Func **');
      Return dst_range;
   END dst_start_stop;

   Function is_dst_now
        Return boolean
      AS
          --type date_array is table of date;
          dst_range date_array;
          curr_time date := LocalTimestamp;
      Begin
          dst_range := date_array();
          dst_range.extend(2);
          dst_range := dst_start_stop(curr_time);
          If (dst_range(1) <= curr_time and curr_time < dst_range(2))  then 
            DBMS_OUTPUT.PUT_LINE('DST is  active.');
            Return True;
          Else
            DBMS_OUTPUT.PUT_LINE('DST is NOT active.');
            Return False;
          End If;  
   End;

   FUNCTION dst_offset (prevailing_date DATE, dst_start DATE, dst_stop DATE) 
        RETURN number
      AS
            offset_days number :=0;
      BEGIN

          DBMS_OUTPUT.PUT_LINE('  Starting dst_offset sub-function:');
          DBMS_OUTPUT.PUT_LINE('        where (input date, DST start, DST stop) = '||to_char(prevailing_date,'mm/dd/yyyy hh24:mi')
                                ||', '||to_char(dst_start,'mm/dd/yyyy hh24:mi')||', '||to_char(dst_stop,'mm/dd/yyyy hh24:mi'));

          If (dst_start <= prevailing_date and prevailing_date < dst_stop) then 
            offset_days :=1/24;
            DBMS_OUTPUT.PUT_LINE('        input date is between dst start and stop');
          Else
            offset_days :=0;
            DBMS_OUTPUT.PUT_LINE('        input date is not between dst start and stop');
          End If;
          DBMS_OUTPUT.PUT_LINE('        Result: DST Offset days = '||offset_days);
          DBMS_OUTPUT.PUT_LINE('                          hours = '||(offset_days*24)); 
          Return offset_days;
   END dst_offset;

-- Begin  --move this down under the function -- ******************

   FUNCTION Convert_TZ (input_date DATE, input_tz varchar2, output_tz varchar2) 
   RETURN date
   AS
        -- Variables
        input_sz varchar(3) := substr(input_TZ,1,1)||'S'||substr(input_TZ,3,1);
        input_sz varchar(3) := substr(output_TZ,1,1)||'S'||substr(output_TZ,3,1);
        temp_str varchar2(1000);
        dst_range date_array;
        input_dst_offset number := 0;
        input_tz_offset number := 0;
        input_date_st date; --standard time
        gmt_date date;
        output_dst_offset number := 0;
        output_tz_offset number := 0;
        output_date date;
        output_date_pt date; -- prevailing time
        tz_offset_str varchar2(30);
        --orig_nls_date_format varchar2(30) := NLS_DATE_FORMAT;
        --TempYear number(4,0) := to_char(TempDate,'YYYY');  -- or := trunc(PrevailingTime, YYYY);

   BEGIN 

      DBMS_OUTPUT.PUT_LINE('Starting Pl/sql procedure.  ');

      DBMS_OUTPUT.PUT_LINE('Input Date: '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz||'.  ');
      -- Find DST start/stop dates  
      dst_range := date_array();
      dst_range.extend(2);
      dst_range := dst_start_stop(input_date);
      DBMS_OUTPUT.PUT_LINE('DST date range determined.  ');
      DBMS_OUTPUT.PUT_LINE('      dst_range(1): '||to_char(dst_range(1),'mm/dd/yyyy hh24:mi')||' = '||dst_range(1));
      DBMS_OUTPUT.PUT_LINE('      dst_range(2): '||to_char(dst_range(2),'mm/dd/yyyy hh24:mi')||' = '||dst_range(2));

      -- Convert Input Date from input time zone to GMT
        If upper(input_TZ) in ('GMT','UCT') then
            -- If input TZ is GMT, we can skip this conversion!
            DBMS_OUTPUT.PUT_LINE('    Input Time is ('||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '
                                  ||input_tz||' GMT).  No conversion required.  ');
            input_tz_offset := 0;
            input_dst_offset := 0; 
            gmt_date := input_date;
        Else
            -- Convert from local prevailing to local standard time
                -- Get input_dst_offset 
                Case upper(substr(input_TZ,2,1)) 
                  When 'S' then 
                    -- already in standard time, not conversion needed.
                    input_dst_offset := 0; -- duplicative
                    DBMS_OUTPUT.PUT_LINE('Standard time ('||input_tz||') entered; no dst offset.' );
                          --input_tz_offset := input_tz_offset;
                  Else 
                    -- run dst_offset function to convert from prevailing or daylight time to standard time.
                    input_dst_offset := dst_offset(input_date, dst_range(1), dst_range(2));
                    input_date_st := input_date - input_dst_offset; 
                    DBMS_OUTPUT.PUT_LINE('Daylight Saving Time Effective ('||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '
                                          ||input_tz||'); 1 hour offset; input DST offset = '||input_dst_offset);
                    DBMS_OUTPUT.PUT_LINE('        where (input_date, dst_start, dst_stop) = '
                                          ||to_char(input_date,'mm/dd/yyyy hh24:mi')
                                          ||' '||input_tz||', '||dst_range(1)||', '||dst_range(2)||', ');
                    DBMS_OUTPUT.PUT_LINE('        which adjusts '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz
                                          ||' daylight to '||input_date_st||' standard time.  ');
                End Case;

            -- Convert from local standard time to GMT
                SELECT TZ_OFFSET((SELECT max(tzname) FROM V$TIMEZONE_NAMES where tzabbrev = input_TZ)) 
                INTO tz_offset_str 
                FROM DUAL;

                input_tz_offset := ( substr(tz_offset_str,1,3)/24 + substr(tz_offset_str,5,2)/1440 );
                If is_dst_now then 
                  input_tz_offset := input_tz_offset - 1/24;
                End if;
                DBMS_OUTPUT.PUT_LINE('    input_tz_offset (fractional days): '||input_tz_offset||'. ');
                DBMS_OUTPUT.PUT_LINE('    input_tz_offset (hours): '||input_tz_offset*24||'. ');
                gmt_date := input_date_st - input_tz_offset;

        End If;

        -- Convert input date from GMT to requested output time zone
        DBMS_OUTPUT.PUT_LINE('  ');
        DBMS_OUTPUT.PUT_LINE('Starting output_date analysis. ');
        If upper(output_TZ) in ('GMT','UCT') then
            -- If desired output TZ is GMT, we can skip this conversion!
            output_tz_offset := 0;
            output_dst_offset := 0; 
            output_date := gmt_date;
            DBMS_OUTPUT.PUT_LINE('    Requested output format is GMT: ('||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||').  No conversion required.  ');
        Else
            -- Get output_dst_offset 
                Case upper(substr(output_TZ,2,1)) 
                  When 'S' then 
                    output_dst_offset := 0; -- duplicative
                    DBMS_OUTPUT.PUT_LINE('Standard time ('||output_TZ||') entered; no dst offset.' );
                  Else
                    output_dst_offset := dst_offset(gmt_date + output_tz_offset, dst_range(1), dst_range(2));
                End Case;
            -- Convert from GMT to local standard time
                SELECT TZ_OFFSET((SELECT max(tzname) FROM V$TIMEZONE_NAMES where tzabbrev = output_TZ)) INTO tz_offset_str FROM DUAL;
                output_tz_offset := ( substr(tz_offset_str,1,3)/24 + substr(tz_offset_str,5,2)/1440 );
                DBMS_OUTPUT.PUT_LINE('    output_tz_offset (fractional days): '||output_tz_offset||'. ');
                DBMS_OUTPUT.PUT_LINE('    output_tz_offset (hours): '||output_tz_offset*24||'. ');
                If is_dst_now then 
                  output_tz_offset := output_tz_offset - 1/24;
                  DBMS_OUTPUT.PUT_LINE('    tz_offset correction... ');
                  DBMS_OUTPUT.PUT_LINE('      output_tz_offset (fractional days): '||output_tz_offset||'. ');
                  DBMS_OUTPUT.PUT_LINE('      output_tz_offset (hours): '||output_tz_offset*24||'. ');
                End if;

                    output_date := gmt_date + output_tz_offset + output_dst_offset;        
                    DBMS_OUTPUT.PUT_LINE('          gmt_date: '||gmt_date);
                    DBMS_OUTPUT.PUT_LINE('  output_tz_offset: '||output_tz_offset);
                    DBMS_OUTPUT.PUT_LINE(' output_dst_offset: '||output_dst_offset);

                    DBMS_OUTPUT.PUT_LINE('Daylight Saving Time ('||output_TZ||') offset = '||output_dst_offset);
                    DBMS_OUTPUT.PUT_LINE('        where (output_date, dst_start, DST_stop) = '||output_date||', '||dst_range(1)||', '||dst_range(2));
                    DBMS_OUTPUT.PUT_LINE('        which adjusts '||output_date||' standard to '||output_date_pt||' daylight time.  ');
        End If;

        DBMS_OUTPUT.PUT_LINE('Output Date = '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||'.  ');

    Goto AllDone;

    <<FoundError>>      

    <<AllDone>>
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('***    Results     ***');
      DBMS_OUTPUT.PUT_LINE('   ');

      if input_dst_offset <> 0 then
        temp_str := 'daylight saving';
      else
        temp_str := 'standard';
      end if;
      DBMS_OUTPUT.PUT_LINE('    Input Date: '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz||', which falls in  '||temp_str||' time.  ');
      DBMS_OUTPUT.PUT_LINE('    GMT Date: '||to_char(gmt_date,'mm/dd/yyyy hh24:mi')||' UTC/GMT.  ');
      DBMS_OUTPUT.PUT_LINE('    Output Date: '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' UTC/GMT.  ');
      if output_dst_offset <> 0 then
        temp_str := 'daylight saving';
      else
        temp_str := 'standard';
      end if;
      DBMS_OUTPUT.PUT_LINE('    All Done.  Return Value: '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||'.  ');
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('*** End of Results ***');
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('LocalTimestamp EPT: '||LocalTimestamp);
      DBMS_OUTPUT.PUT_LINE('LocalTimestamp GMT:'||LOCALTIMESTAMP at time zone '+00:00');

      Return output_date;
   END;  
/*********************** End of Declarations of Variables and Functions for Time Zone Conversion (Convert_TZ function) *********************/  
Begin  
/*********************** Start of Procedural Code for  Time Zone Conversion (Convert_TZ function) *********************/  
     /* DOW list is required for Time Zone Conversion (Convert_TZ function) and should not be deleted. */
      dow_list := str_array();
      dow_list.extend(7);
      dow_list(1) := 'Sun';
      dow_list(2) := 'Mon';
      dow_list(3) := 'Tue';
      dow_list(4) := 'Wed';
      dow_list(5) := 'Thu';
      dow_list(6) := 'Fri';
      dow_list(7) := 'Sat';

     /* Next 2 lines are example of use of Time Zone Conversion (Convert_TZ function). */
      return_date := Convert_TZ (input_date, input_tz, output_tz);
      DBMS_OUTPUT.PUT_LINE('ta-dah! '||return_date);
/*********************** End of Procedural Code for Time Zone Conversion (Convert_TZ function) *********************/  

-- ******** User coded begins here ******** --
End;

Mark m · Answer 7 · 2014-11-05T21:24:53+0000

RTRIM( TO_CHAR( systimestamp at time zone 'GMT', 'DD-MON-YYYY HH24:MI:SS TZR' ))
RTRIM( TO_CHAR( systimestamp at time zone 'EST', 'DD-MON-YYYY HH24:MI:SS TZR' ))
RTRIM( TO_CHAR( systimestamp at time zone 'PST', 'DD-MON-YYYY HH24:MI:SS TZR' ))

output:
   05-NOV-2014 17:20:10 GMT
   05-NOV-2014 12:20:10 EST
   05-NOV-2014 09:20:10 PST

substr('05-NOV-2014 09:20:10 PST', -3, 3)   will return 'PST'

RTRIM (EXTRACT (HOUR from systimestamp ))
output:  17    (notice the 17 is in GMT time)

Convert timezone to PLSQL

More articles: