Tuesday, May 2, 2017

Oracle Datetime (5) - SQL Arithmetic

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


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

We can see all arithmetic with INTERVAL maintains original data types, but arithmetic's with NUMBER will always convert to data type DATE. One strange case is LTZ_DB_DUMP is with Typ=231, but LTZ_DB_INTV_DUMP with Typ=232.

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

The above output shows that DIFF_A2_A1 (02:03) is a purely math subtraction, and all other subtractions (01:03) are performed in natural sense.