On Oracle: Oracle Datetime (3)

(1)-Concepts (2)-Examples (3)-Assignments (4)-Comparisons (5)-SQL Arithmetic (6)-PLSQL Arithmetic

In this Blog, we will look 4 possible Datetime assignments:

    (1) PLSQL := PLSQL
    (2) SQL   :=   SQL
    (3) PLSQL :=   SQL
    (4) SQL   := PLSQL

For each combination, run the Test Code and look the Output.

1. PLSQL Assignments


--------------- Test Code --------------- 

create or replace procedure test_assignment_plsql as
  l_timestp      TIMESTAMP;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From l_timestp to l_timestp_ltz, l_timestp_tz -------');
  l_timestp     := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_tz  := l_timestp;    -- append sessiontimezone
  l_timestp_ltz := l_timestp;    -- append sessiontimezone
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
  
  dbms_output.put_line('------- From l_timestp_ltz to l_timestp, l_timestp_tz -------');
  l_timestp_ltz := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD'); -- convert to sessiontimezone
  l_timestp     := l_timestp_ltz;   -- copy Time, drop Zone
  l_timestp_tz  := l_timestp_ltz;   -- copy, l_timestp_ltz = l_timestp_tz
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));          
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD')); 
  dbms_output.put_line('');
   
  dbms_output.put_line('------- From l_timestp_tz to l_timestp, l_timestp_ltz -------');
  l_timestp_tz  := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp     := l_timestp_tz;    -- copy Time, drop Zone, not convertion
  l_timestp_ltz := l_timestp_tz;    -- convert to sessiontimezone, l_timestp_ltz != l_timestp_tz
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp     = ' ||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
end;
/

alter session set time_zone = 'Europe/London'; 
exec test_assignment_plsql;

--------------- Test Output ---------------

  ------- From l_timestp to l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 05-APR-2017 02:52:00
  l_timestp_tz  = 05-APR-2017 02:52:00 EUROPE/LONDON BST
  l_timestp_ltz = 05*APR*2017 02:52:00 EUROPE/LONDON BST
  
  ------- From l_timestp_ltz to l_timestp, l_timestp_tz -------
  l_timestp_ltz = 05*APR*2017 01:52:00 EUROPE/LONDON BST
  l_timestp     = 05-APR-2017 01:52:00
  l_timestp_tz  = 05-APR-2017 01:52:00 EUROPE/LONDON BST
  
  ------- From l_timestp_tz to l_timestp, l_timestp_ltz -------
  l_timestp_tz  = 05-APR-2017 02:52:00 EUROPE/ZURICH CEST
  l_timestp     = 05-APR-2017 02:52:00
  l_timestp_ltz = 05*APR*2017 01:52:00 EUROPE/LONDON BST

In summary, we can say:

  (1). TO and FROM TIMESTAMP are pure copy of time portion, if Zone needed, take sessiontimezone as default Zone.
  (2). TIMESTAMP WITH LOCAL TIME ZONE to TIMESTAMP WITH TIME ZONE is string pure copy with sessiontimezone 
       as default Zone.
  (3). TIMESTAMP WITH TIME ZONE to TIMESTAMP WITH LOCAL TIME ZONE is conversion from Source Time Zone 
       to Target Time Zone (sessiontimezone).
       This is the only place where Time Zone is involved. All others are just pure String Shallow Copy.

2. SQL Assignments

Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.


--------------- Test Code --------------- 

drop table tz_test_tab;

create table tz_test_tab 
(name      VARCHAR2(15),
 loc_zone  VARCHAR2(15), 
 sys_date  DATE,
 sys_tz    TIMESTAMP WITH TIME ZONE, 
 loc_date  DATE,
 loc_ltz   TIMESTAMP WITH LOCAL TIME ZONE,
 loc_tz    TIMESTAMP WITH TIME ZONE, 
 utc       TIMESTAMP);

ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MON-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='YYYY*MON*DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MON-DD HH24:MI:SS TZR TZD';

col name     format a15
col loc_zone format a15
col sys_date format a25
col sys_tz   format a40
col loc_date format a25
col loc_ltz  format a35
col loc_tz   format a48
col utc      format a35

alter session set time_zone = 'Europe/Zurich';
insert into tz_test_tab values('Zurich', sessiontimezone, sysdate, systimestamp, 
                                current_date, localtimestamp, current_timestamp, sys_extract_utc(systimestamp));
                            
alter session set time_zone = 'Europe/London';
insert into tz_test_tab values('London', sessiontimezone, sysdate, systimestamp, 
                                current_date, localtimestamp, current_timestamp, sys_extract_utc(systimestamp));   

commit;

alter session set time_zone = 'Europe/Zurich';
select * from tz_test_tab;   

--------------- Test Output ---------------

  NAME             : Zurich
  LOC_ZONE         : Europe/Zurich
  SYS_DATE         : 2017-MAY-02 09:20:02
  SYS_TZ           : 2017-MAY-02 09:20:02 +02:00
  LOC_DATE         : 2017-MAY-02 09:20:02
  LOC_LTZ          : 2017*MAY*02 09:20:02
  LOC_TZ           : 2017-MAY-02 09:20:02 EUROPE/ZURICH CEST
  UTC              : 2017*MAY*02 07:20:02
  -----------------
  NAME             : London
  LOC_ZONE         : Europe/London
  SYS_DATE         : 2017-MAY-02 09:20:02
  SYS_TZ           : 2017-MAY-02 09:20:02 +02:00
  LOC_DATE         : 2017-MAY-02 08:20:02
  LOC_LTZ          : 2017*MAY*02 09:20:02
  LOC_TZ           : 2017-MAY-02 08:20:02 EUROPE/LONDON BST
  UTC              : 2017*MAY*02 07:20:02

From above output, we can see only loc_ltz is sessiontimezone sensitive, for example, for row of London, when time_zone = 'Europe/Zurich', it is converted to sessiontimezone.

3. SQL to PLSQL Assignments

Continue from above example, and assign row 'Zurich' to PLSQL.


--------------- Test Code --------------- 

create or replace procedure test_assignment_sql2plsql as
  l_timestp      DATE;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From sys_date to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select sys_date, sys_date, sys_date into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
  
  dbms_output.put_line('------- From loc_ltz to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select loc_ltz, loc_ltz, loc_ltz into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
   
  dbms_output.put_line('------- From sys_tz to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select sys_tz, sys_tz, sys_tz into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
end;
/

alter session set time_zone = 'Europe/London';
exec test_assignment_sql2plsql;

--------------- Test Output ---------------

  ------- From sys_date to l_timestp, l_timestp_ltz, l_timestp_tz ------
  l_timestp     = 02-MAY-2017 09:20:02
  l_timestp_tz  = 02-MAY-2017 09:20:02 EUROPE/LONDON BST
  l_timestp_ltz = 02*MAY*2017 09:20:02 EUROPE/LONDON BST
  
  ------- From loc_ltz to l_timestp, l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 02-MAY-2017 08:20:02
  l_timestp_tz  = 02-MAY-2017 08:20:02 EUROPE/LONDON BST
  l_timestp_ltz = 02*MAY*2017 08:20:02 EUROPE/LONDON BST
  
  ------- From sys_tz to l_timestp, l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 02-MAY-2017 09:20:02
  l_timestp_tz  = 02-MAY-2017 09:20:02 +02:00
  l_timestp_ltz = 02*MAY*2017 08:20:02 EUROPE/LONDON BST

The above output shows that when we assign sys_tz to l_timestp_tz, time is converted from Zurich to London.

4. PLSQL to SQL Assignments


--------------- Test Code --------------- 

create or replace procedure test_assignment_plsql2sql as
  l_timestp      TIMESTAMP;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From l_timestp to sys_date, loc_ltz, loc_tz -------');
  l_timestp     := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp', l_timestp, l_timestp, l_timestp);
  
  dbms_output.put_line('------- From l_timestp_ltz to sys_date, loc_ltz, loc_tz -------');
  l_timestp_ltz := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD'); 
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp_ltz', l_timestp_ltz, l_timestp_ltz, l_timestp_ltz);
   
  dbms_output.put_line('------- From l_timestp_tz to sys_date, loc_ltz, loc_tz -------');
  l_timestp_tz  := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp_tz', l_timestp_tz, l_timestp_tz, l_timestp_tz);
  commit;
end;
/

truncate table tz_test_tab;

alter session set time_zone = 'Europe/Zurich';
exec test_assignment_plsql2sql;

alter session set time_zone = 'Europe/London';
select name, sys_date, loc_ltz, loc_tz from tz_test_tab;  

  --------------- Test Output ---------------
  
  NAME             : l_timestp
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
  -----------------
  NAME             : l_timestp_ltz
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
  -----------------
  NAME             : l_timestp_tz
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST

The above output shows that all loc_ltz are adjusted to the sessiontimezone.

On Oracle

Tuesday, May 2, 2017

Oracle Datetime (3) - Assignments

1. PLSQL Assignments

2. SQL Assignments

3. SQL to PLSQL Assignments

4. PLSQL to SQL Assignments