SQL 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
Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.
Run Test Code below:
--------------- Test Code ---------------
alter session set time_zone = 'Europe/Zurich';
drop table sql_test_tab_add;
create table sql_test_tab_add (loc_ltz TIMESTAMP WITH LOCAL TIME ZONE);
insert into sql_test_tab_add values(localtimestamp);
commit;
drop view sql_test_tab_add_view;
create view sql_test_tab_add_view as
with base as
(select 5/1440 num
,numtodsinterval(7, 'Minute') intv
,to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss') timestp_base
,(to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss') at time zone sessiontimezone) ltz_base
,to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD') tz_base
,(select loc_ltz from sql_test_tab_add where rownum=1) ltz_db
from dual
)
,base_ops as
(select base.*
,timestp_base + num timestp_base_num
,timestp_base + intv timestp_base_intv
,tz_base + num tz_base_num
,tz_base + intv tz_base_intv
,ltz_db + num ltz_db_num
,ltz_db + intv ltz_db_intv
from base
)
select
num ,dump(num, 1016) num_dump -- Typ=2 SQLT_NUM
,intv ,dump(intv, 1016) intv_dump -- Typ=190 SQLT_INTVERVAL_DS
,timestp_base ,dump(timestp_base, 1016) timestp_base_dump -- Typ=187 SQLT_TIMESTAMP
,ltz_base ,dump(ltz_base, 1016) ltz_base_dump -- Typ=188 SQLT_TIMESTAMP_TZ
,tz_base ,dump(tz_base, 1016) tz_base_dump -- Typ=188 SQLT_TIMESTAMP_TZ
,ltz_db ,dump(ltz_db, 1016) ltz_db_dump -- Typ=231 SQLT_TIMESTAMP_LTZ
-------------------- NUMBER and INTERVAL Arithmetic ------------------------
,timestp_base_num ,dump(timestp_base_num, 1016) timestp_base_num_dump -- Typ=13 SQLT_DATE
,timestp_base_intv ,dump(timestp_base_intv, 1016) timestp_base_intv_dump -- Typ=187 SQLT_TIMESTAMP
,tz_base_num ,dump(tz_base_num, 1016) tz_base_num_dump -- Typ=13 SQLT_DATE
,tz_base_intv ,dump(tz_base_intv, 1016) tz_base_intv_dump -- Typ=188 SQLT_TIMESTAMP_TZ
,ltz_db_num ,dump(ltz_db_num, 1016) ltz_db_num_dump -- Typ=13 SQLT_DATE
,ltz_db_intv ,dump(ltz_db_intv, 1016) ltz_db_intv_dump -- Typ=232 SQLT_TIMESTAMP_LTZ
from base_ops;
alter session set time_zone = 'Europe/London';
select * from sql_test_tab_add_view;
--------------- Test Output ---------------
NUM : .003472222222222222222222222222222222222
NUM_DUMP : Typ=2 Len=21: bf,23,49,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17
INTV : +000000000 00:07:00.000000000
INTV_DUMP : Typ=190 Len=24: 0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,a,0,0,0
TIMESTP_BASE : 2017*APR*05 02:52:00
TIMESTP_BASE_DUMP : Typ=187 Len=20: e1,7,4,5,2,34,0,0,0,0,0,0,0,0,3,0,0,0,0,0
LTZ_BASE : 2017-APR-05 02:52:00 EUROPE/LONDON BST
LTZ_BASE_DUMP : Typ=188 Len=20: e1,7,4,5,1,34,0,0,0,0,0,0,1,0,5,0,71,1,0,0
TZ_BASE : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
TZ_BASE_DUMP : Typ=188 Len=20: e1,7,4,5,0,34,0,0,0,0,0,0,2,0,5,0,96,1,0,0
LTZ_DB : 2017*MAY*02 08:39:08
LTZ_DB_DUMP : Typ=231 Len=11: 78,75,5,2,9,28,9,39,94,86,38
TIMESTP_BASE_NUM : 2017-APR-05 02:57:00
TIMESTP_BASE_NUM_DUMP : Typ=13 Len=8: e1,7,4,5,2,39,0,0
TIMESTP_BASE_INTV : 2017*APR*05 02:59:00
TIMESTP_BASE_INTV_DUMP : Typ=187 Len=20: e1,7,4,5,2,3b,0,0,0,0,0,0,0,0,3,0,0,0,0,0
TZ_BASE_NUM : 2017-APR-05 02:57:00
TZ_BASE_NUM_DUMP : Typ=13 Len=8: e1,7,4,5,2,39,0,0
TZ_BASE_INTV : 2017-APR-05 02:59:00 EUROPE/ZURICH CEST
TZ_BASE_INTV_DUMP : Typ=188 Len=20: e1,7,4,5,0,3b,0,0,0,0,0,0,2,0,5,0,96,1,0,0
LTZ_DB_NUM : 2017-MAY-02 08:44:08
LTZ_DB_NUM_DUMP : Typ=13 Len=8: e1,7,5,2,8,2c,8,0
LTZ_DB_INTV : 2017*MAY*02 08:46:08
LTZ_DB_INTV_DUMP : Typ=232 Len=20: e1,7,5,2,8,2e,8,0,38,86,94,39,1,0,7,0,0,0,0,0
This is well documented in Datetime Data Types and Time Zone Support - Datetime and Interval Arithmetic:
You can use NUMBER constants in arithmetic operations on date and timestamp values. Oracle Database internally converts timestamp values to date values before doing arithmetic operations on them with NUMBER constants.
2. Substract Arithmetic
Run code below:
--------------- Test Code ---------------
drop table sql_test_tab_substract;
create table sql_test_tab_substract (
a1 TIMESTAMP
,a2 TIMESTAMP
,diff_a2_a1 INTERVAL DAY TO SECOND
,ltz_a1 TIMESTAMP WITH LOCAL TIME ZONE
,ltz_a2 TIMESTAMP WITH LOCAL TIME ZONE
,diff_ltz_a2_a1 INTERVAL DAY TO SECOND
,tz_a1 TIMESTAMP WITH TIME ZONE
,tz_a2 TIMESTAMP WITH TIME ZONE
,diff_tz_a2_a1 INTERVAL DAY TO SECOND
,ltz_b1 TIMESTAMP WITH LOCAL TIME ZONE
,ltz_b2 TIMESTAMP WITH LOCAL TIME ZONE
,diff_ltz_b2_b1 INTERVAL DAY TO SECOND
,tz_b1 TIMESTAMP WITH TIME ZONE
,tz_b2 TIMESTAMP WITH TIME ZONE
,diff_tz_b2_b1 INTERVAL DAY TO SECOND
);
create or replace procedure sql_test_tab_substract_insert 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_timestp_tz_b1 TIMESTAMP WITH TIME ZONE;
l_timestp_tz_b2 TIMESTAMP WITH TIME ZONE;
l_interval INTERVAL DAY TO SECOND;
l_dump varchar2(50);
begin
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');
l_timestp_ltz_b1 := l_timestp_tz_a1;
l_timestp_ltz_b2 := l_timestp_tz_a2;
l_timestp_tz_b1 := l_timestp_ltz_a1;
l_timestp_tz_b2 := l_timestp_ltz_a2;
insert into sql_test_tab_substract values (
l_timestp_a1
,l_timestp_a2
,(l_timestp_a2 - l_timestp_a1)
,l_timestp_ltz_a1
,l_timestp_ltz_a2
,(l_timestp_ltz_a2 - l_timestp_ltz_a1)
,l_timestp_tz_a1
,l_timestp_tz_a2
,(l_timestp_tz_a2 - l_timestp_tz_a1)
,l_timestp_ltz_b1
,l_timestp_ltz_b2
,(l_timestp_ltz_b2 - l_timestp_ltz_b1)
,l_timestp_tz_b1
,l_timestp_tz_b2
,(l_timestp_tz_b2 - l_timestp_tz_b1)
);
commit;
end;
/
alter session set time_zone = 'Europe/Zurich';
exec sql_test_tab_substract_insert;
select * from sql_test_tab_substract;
--------------- Test Output ---------------
A1 : 2017*MAR*26 01:52:00
A2 : 2017*MAR*26 03:55:00
DIFF_A2_A1 : +00 02:03:00.000000
LTZ_A1 : 2017*MAR*26 01:52:00
LTZ_A2 : 2017*MAR*26 03:55:00
DIFF_LTZ_A2_A1 : +00 01:03:00.000000
TZ_A1 : 2017-MAR-26 01:52:00 EUROPE/ZURICH CET
TZ_A2 : 2017-MAR-26 03:55:00 EUROPE/ZURICH CEST
DIFF_TZ_A2_A1 : +00 01:03:00.000000
LTZ_B1 : 2017*MAR*26 01:52:00
LTZ_B2 : 2017*MAR*26 03:55:00
DIFF_LTZ_B2_B1 : +00 01:03:00.000000
TZ_B1 : 2017-MAR-26 01:52:00 EUROPE/ZURICH CET
TZ_B2 : 2017-MAR-26 03:55:00 EUROPE/ZURICH CEST
DIFF_TZ_B2_B1 : +00 01:03:00.000000
