PLSQL Datetime Arithmetic operators include:
(1) One operand is datetime, another is number or interval, for example, Addition
(2) Both operands are datetime, for example, Subtraction
For each case, run the Test Code and look the Output.1. Number and Interval Arithmetic
Run code below:
--------------- Test Code ---------------
create or replace procedure test_arithmetic_plsql as
l_timestp_base TIMESTAMP;
l_timestp_ltz_base TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_tz_base TIMESTAMP WITH TIME ZONE;
l_timestp TIMESTAMP;
l_timestp_ltz TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_tz TIMESTAMP WITH TIME ZONE;
l_intv INTERVAL DAY TO SECOND;
l_num NUMBER;
l_dump varchar2(100);
begin
l_num := 5/1440;
l_intv := numtodsinterval(7, 'Minute');
l_timestp_base := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
l_timestp_ltz_base := (l_timestp_base at time zone sessiontimezone);
l_timestp_tz_base := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
dbms_output.put_line('------ 1. TIMESTAMP Arithmetics ------');
l_timestp := l_timestp_base;
dbms_output.put_line('l_timestp = '||to_char(l_timestp, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp := l_timestp_base + l_num;
dbms_output.put_line('l_timestp+l_num = '||to_char(l_timestp, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp+l_num) DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp := l_timestp_base + l_intv;
dbms_output.put_line('l_timestp+l_intv = '||to_char((l_timestp), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp+l_intv) DUMP : '||l_dump);
dbms_output.put_line('');
dbms_output.put_line('------ 2. TIMESTAMP WITH LOCAL TIME ZONE Arithmetics ------');
l_timestp_ltz := l_timestp_ltz_base;
dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp_ltz := l_timestp_ltz_base+5/1440;
dbms_output.put_line('l_timestp_ltz = '||to_char((l_timestp_ltz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp_ltz), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp_ltz+l_num) DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp_ltz := l_timestp_ltz_base+l_intv;
dbms_output.put_line('l_timestp_ltz = '||to_char((l_timestp_ltz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp_ltz), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp_ltz+l_intv) DUMP : '||l_dump);
dbms_output.put_line('');
dbms_output.put_line('------ 3. TIMESTAMP WITH TIME ZONE Arithmetics ------');
l_timestp_tz := l_timestp_tz_base;
dbms_output.put_line('l_timestp_tz = '||to_char(l_timestp_tz, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_tz, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_tz DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp_tz := l_timestp_tz_base+5/1440;
dbms_output.put_line('l_timestp_tz = '||to_char((l_timestp_tz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp_tz), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp_tz+l_num) DUMP : '||l_dump);
dbms_output.put_line('');
l_timestp_tz := l_timestp_tz_base+l_intv;
dbms_output.put_line('l_timestp_tz = '||to_char((l_timestp_tz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump((l_timestp_tz), 1016)) into l_dump from dual;
dbms_output.put_line('(l_timestp_tz+l_intv) DUMP : '||l_dump);
dbms_output.put_line('');
end;
/
alter session set time_zone = 'Europe/Zurich';
exec test_arithmetic_plsql;
--------------- Test Output ---------------
------ 1. TIMESTAMP Arithmetics ------
l_timestp = 05*APR*2017 02:52:00 +00:00
l_timestp DUMP : Typ=180 Len=7: 78,75,4,5,3,35,1
l_timestp+l_num = 05*APR*2017 02:57:00 +00:00
(l_timestp+l_num) DUMP : Typ=180 Len=7: 78,75,4,5,3,3a,1
l_timestp+l_intv = 05*APR*2017 02:59:00 +00:00
(l_timestp+l_intv) DUMP : Typ=180 Len=7: 78,75,4,5,3,3c,1
------ 2. TIMESTAMP WITH LOCAL TIME ZONE Arithmetics ------
l_timestp_ltz = 05*APR*2017 02:52:00 EUROPE/ZURICH CEST
l_timestp_ltz DUMP : Typ=231 Len=7: 78,75,4,5,2,35,1
l_timestp_ltz = 05*APR*2017 02:57:00 EUROPE/ZURICH CEST
(l_timestp_ltz+l_num) DUMP : Typ=231 Len=7: 78,75,4,5,2,3a,1
l_timestp_ltz = 05*APR*2017 02:59:00 EUROPE/ZURICH CEST
(l_timestp_ltz+l_intv) DUMP : Typ=231 Len=7: 78,75,4,5,2,3c,1
------ 3. TIMESTAMP WITH TIME ZONE Arithmetics ------
l_timestp_tz = 05*APR*2017 02:52:00 EUROPE/ZURICH CEST
l_timestp_tz DUMP : Typ=181 Len=13: 78,75,4,5,1,35,1,0,0,0,0,86,58
l_timestp_tz = 05*APR*2017 02:57:00 EUROPE/ZURICH CEST
(l_timestp_tz+l_num) DUMP : Typ=181 Len=13: 78,75,4,5,1,3a,1,0,0,0,0,86,58
l_timestp_tz = 05*APR*2017 02:59:00 EUROPE/ZURICH CEST
(l_timestp_tz+l_intv) DUMP : Typ=181 Len=13: 78,75,4,5,1,3c,1,0,0,0,0,86,58
2. Substract Arithmetic
Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.
Run code below:
--------------- Test Code ---------------
create or replace procedure test_subtract_plsql as
-- 2017-03-26 [02:00, 03:00) DST transit time
l_a1 varchar2(100) := '2017-03-26 01:52:00';
l_a2 varchar2(100) := '2017-03-26 03:55:00';
l_timestp_a1 TIMESTAMP;
l_timestp_a2 TIMESTAMP;
l_timestp_ltz_a1 TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_ltz_a2 TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_tz_a1 TIMESTAMP WITH TIME ZONE;
l_timestp_tz_a2 TIMESTAMP WITH TIME ZONE;
l_timestp_ltz_b1 TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_ltz_b2 TIMESTAMP WITH LOCAL TIME ZONE;
l_interval INTERVAL DAY TO SECOND;
l_dump varchar2(50);
begin
dbms_output.put_line('a1 = '||l_a1);
dbms_output.put_line('a2 = '||l_a2);
dbms_output.put_line('');
l_timestp_a1 := to_timestamp(l_a1, 'yyyy-mm-dd hh24:mi:ss');
l_timestp_a2 := to_timestamp(l_a2, 'yyyy-mm-dd hh24:mi:ss');
l_timestp_ltz_a1 := l_timestp_a1;
l_timestp_ltz_a2 := l_timestp_a2;
l_timestp_tz_a1 := to_timestamp_tz(l_a1||' Europe/Zurich CET', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
l_timestp_tz_a2 := to_timestamp_tz(l_a2||' Europe/Zurich CEST', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
dbms_output.put_line('------ l_timestp_a2 - l_timestp_a1 ------');
l_interval := l_timestp_a2 - l_timestp_a1;
dbms_output.put_line('TIMESTAMP Subtract = '||l_interval);
dbms_output.put_line('');
dbms_output.put_line('------ l_timestp_ltz_a2 - l_timestp_ltz_a1 ------');
l_interval := l_timestp_ltz_a2 - l_timestp_ltz_a1;
dbms_output.put_line('LOCAL TIME ZONE (a2 - a1) = '||l_interval);
dbms_output.put_line(' a1 = '||to_char(l_timestp_ltz_a1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_a1, 1016)) into l_dump from dual;
dbms_output.put_line(' a1 Dump : '||l_dump);
dbms_output.put_line(' a2 = '||to_char(l_timestp_ltz_a2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_a2, 1016)) into l_dump from dual;
dbms_output.put_line(' a2 Dump : '||l_dump);
select (dump(l_interval, 1016)) into l_dump from dual;
dbms_output.put_line(' (a2 - a1) Dump : '||l_dump);
dbms_output.put_line('');
dbms_output.put_line('------ l_timestp_ltz_b2 - l_timestp_ltz_b1 by TSZ assign ------');
l_timestp_ltz_b1 := l_timestp_tz_a1;
l_timestp_ltz_b2 := l_timestp_tz_a2;
l_interval := l_timestp_ltz_b2 - l_timestp_ltz_b1;
dbms_output.put_line('LOCAL TIME ZONE by TZ Assign (b2 - b1) = '||l_interval);
dbms_output.put_line(' b1 = '||to_char(l_timestp_ltz_b1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_b1, 1016)) into l_dump from dual;
dbms_output.put_line(' b1 Dump : '||l_dump);
dbms_output.put_line(' b2 = '||to_char(l_timestp_ltz_b2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_b2, 1016)) into l_dump from dual;
dbms_output.put_line(' b2 Dump : '||l_dump);
select (dump(l_interval, 1016)) into l_dump from dual;
dbms_output.put_line(' (b2 - b1) Dump : '||l_dump);
dbms_output.put_line('');
dbms_output.put_line('------ l_timestp_ltz_a1 - l_timestp_ltz_b1, l_timestp_ltz_a2 - l_timestp_ltz_b2 ------');
l_interval := l_timestp_ltz_a1 - l_timestp_ltz_b1;
dbms_output.put_line('(a1 - b1) = '||l_interval);
select (dump(l_interval, 1016)) into l_dump from dual;
dbms_output.put_line(' (a1 - b1) Dump : '||l_dump);
l_interval := l_timestp_ltz_a2 - l_timestp_ltz_b2;
dbms_output.put_line('(a2 - b2) = '||l_interval);
select (dump(l_interval, 1016)) into l_dump from dual;
dbms_output.put_line(' (a2 - b2) Dump : '||l_dump);
dbms_output.put_line('');
dbms_output.put_line('------ l_timestp_tz_a2 - l_timestp_tz_a1 ------');
l_interval := l_timestp_tz_a2 - l_timestp_tz_a1;
dbms_output.put_line('TIME ZONE (a2 - a1) = '||l_interval);
dbms_output.put_line('');
dbms_output.put_line('------ l_timestp_tz_a2 - l_timestp_tz_a1 by TLZ assign ------');
l_timestp_tz_a1 := l_timestp_ltz_a1;
l_timestp_tz_a2 := l_timestp_ltz_a2;
l_interval := l_timestp_tz_a2 - l_timestp_tz_a1;
dbms_output.put_line('TIME ZONE by Local Assign (a2 - a1) = '||l_interval);
dbms_output.put_line(' a1 = '||to_char(l_timestp_tz_a1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
dbms_output.put_line(' a2 = '||to_char(l_timestp_tz_a2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
end;
/
alter session set time_zone = 'Europe/Zurich';
exec test_subtract_plsql;
--------------- Test Output ---------------
a1 = 2017-03-26 01:52:00
a2 = 2017-03-26 03:55:00
------ l_timestp_a2 - l_timestp_a1 ------
TIMESTAMP Subtract = +00 02:03:00.000000
------ l_timestp_ltz_a2 - l_timestp_ltz_a1 ------
LOCAL TIME ZONE (a2 - a1) = +00 02:03:00.000000
a1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
a1 Dump : Typ=231 Len=7: 78,75,3,1a,2,35,1
a2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
a2 Dump : Typ=231 Len=7: 78,75,3,1a,3,38,1
(a2 - a1) Dump : Typ=183 Len=11: 80,0,0,0,3e,3f,3c,80,0,0,0
------ l_timestp_ltz_b2 - l_timestp_ltz_b1 by TSZ assign ------
LOCAL TIME ZONE by TZ Assign (b2 - b1) = +00 01:03:00.000000
b1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
b1 Dump : Typ=231 Len=7: 78,75,3,1a,2,35,1
b2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
b2 Dump : Typ=231 Len=7: 78,75,3,1a,3,38,1
(b2 - b1) Dump : Typ=183 Len=11: 80,0,0,0,3d,3f,3c,80,0,0,0
------ l_timestp_ltz_a1 - l_timestp_ltz_b1, l_timestp_ltz_a2 - l_timestp_ltz_b2
(a1 - b1) = +00 00:00:00.000000
(a1 - b1) Dump : Typ=183 Len=11: 80,0,0,0,3c,3c,3c,80,0,0,0
(a2 - b2) = +00 00:00:00.000000
(a2 - b2) Dump : Typ=183 Len=11: 80,0,0,0,3c,3c,3c,80,0,0,0
------ l_timestp_tz_a2 - l_timestp_tz_a1 ------
TIME ZONE (a2 - a1) = +00 01:03:00.000000
------ l_timestp_tz_a2 - l_timestp_tz_a1 by TLZ assign ------
TIME ZONE by Local Assign (a2 - a1) = +00 01:03:00.000000
a1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
a2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
(a2 - a1) = +00 02:03:00.000000 (b2 - b1) = +00 01:03:00.000000although
(a1 - b1) = +00 00:00:00.000000 (a2 - b2) = +00 00:00:00.000000and their dumps also confirmed the equivalence.
