Tuesday, May 2, 2017

Oracle Datetime (4) - Comparisons

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


There are 3 possible Datetime relational comparisons:
    (1) PLSQL vs. PLSQL                   
    (2) SQL   vs.   SQL                   
    (3) SQL   vs. PLSQL (or PLSQL vs. SQL)
For each combination, run the Test Code and look the Output.

1. PLSQL Comparisons


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

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

create or replace procedure test_comparison_plsql as
  l_timestp_1      TIMESTAMP;   
  l_timestp_2      TIMESTAMP;   
  l_timestp_ltz_1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_1   TIMESTAMP WITH TIME ZONE;
  l_timestp_tz_2   TIMESTAMP WITH TIME ZONE;
  l_interval       INTERVAL DAY TO SECOND;
  l_str            varchar2(40);
begin
  l_interval   := numtodsinterval(15,  'Minute');
  l_timestp_1  := to_timestamp('2017-03-26 01:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_2  := l_timestp_1 + l_interval;
  dbms_output.put_line('l_timestp_1     = '||to_char(l_timestp_2,'DD*MON*YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_2     = '||to_char(l_timestp_2,'DD*MON*YYYY HH24:MI:SS'));
  
  l_timestp_ltz_1  := to_timestamp('2017-03-26 01:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_ltz_2  := l_timestp_ltz_1 + l_interval;
  dbms_output.put_line('l_timestp_ltz_1 = '||to_char(l_timestp_ltz_1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp_ltz_2 = '||to_char(l_timestp_ltz_2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  
  l_timestp_tz_1 := to_timestamp_tz('2017-03-26 01:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp_tz_2 := l_timestp_tz_1 + l_interval;
  dbms_output.put_line('l_timestp_tz_1  = '||to_char(l_timestp_tz_1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp_tz_2  = '||to_char(l_timestp_tz_2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- CASE Comparisons ---------');
  case 
    when l_timestp_1     < l_timestp_2     and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_2     WIN');
    when l_timestp_ltz_1 < l_timestp_ltz_2 and FALSE then dbms_output.put_line('l_timestp_ltz_1 < l_timestp_ltz_2 WIN');
    when l_timestp_tz_1  < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_tz_1  < l_timestp_tz_2  WIN');
    when l_timestp_1     < l_timestp_ltz_2 and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_ltz_2 WIN');
    when l_timestp_1     < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_tz_2  WIN');
    when l_timestp_ltz_1 < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_ltz_1 < l_timestp_tz_2  WIN');
    else dbms_output.put_line('CASE Comparisons OK');
  end case;
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_2 vs l_timestp_ltz_2 ---------');
  l_str := 'l_timestp_2 < l_timestp_ltz_2 ';
  begin
    l_interval := l_timestp_2 - l_timestp_ltz_2;           -- diff ORA-01878
    dbms_output.put_line('l_timestp_2 - l_timestp_ltz_2 = '||l_interval);
    if l_timestp_2 < l_timestp_ltz_2 then               
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
   
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_ltz_2 vs l_timestp_tz_2 ---------');
  l_str := 'l_timestp_ltz_2 < l_timestp_tz_2 ';
  begin
    l_interval := l_timestp_ltz_2 - l_timestp_tz_2;        -- diff OK
    dbms_output.put_line('l_timestp_ltz_2 - l_timestp_tz_2 = '||l_interval);
    if l_timestp_ltz_2 < l_timestp_tz_2 then               -- Comparison ORA-01878
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_tz_2 vs l_timestp_2 ---------');
  l_str := 'l_timestp_tz_2 < l_timestp_2 ';
  begin
    l_interval := l_timestp_tz_2 - l_timestp_2;            -- diff ORA-01878
    dbms_output.put_line('l_timestp_tz_2 - l_timestp_2 = '||l_interval);
    if l_timestp_tz_2 < l_timestp_2 then                    
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
end;
/

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

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

  l_timestp_1     = 26*MAR*2017 02:07:00
  l_timestp_2     = 26*MAR*2017 02:07:00
  l_timestp_ltz_1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
  l_timestp_ltz_2 = 26*MAR*2017 03:07:00 EUROPE/ZURICH CET
  l_timestp_tz_1  = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
  l_timestp_tz_2  = 26*MAR*2017 03:07:00 EUROPE/ZURICH CEST
  
  --------- CASE Comparisons ---------
  CASE Comparisons OK
  
  --------- l_timestp_2 vs l_timestp_ltz_2 ---------
  l_timestp_2 < l_timestp_ltz_2 *** ORA-01878: specified field not found in datetime or interval
  
  --------- l_timestp_ltz_2 vs l_timestp_tz_2 ---------
  l_timestp_ltz_2 - l_timestp_tz_2 = +00 00:00:00.000000
  l_timestp_ltz_2 < l_timestp_tz_2 *** ORA-01878: specified field not found in datetime or interval
  
  --------- l_timestp_tz_2 vs l_timestp_2 ---------
  l_timestp_tz_2 < l_timestp_2 *** ORA-01878: specified field not found in datetime or interval

Look the above output, we can see:
(1). For l_timestp_2 vs l_timestp_ltz_2, ORA-01878 appears when compute:
         l_interval := l_timestp_2 - l_timestp_ltz_2
     because it is trying to convert l_timestp_2 to Time Zone "EUROPE/ZURICH".
   
(2). For l_timestp_ltz_2 vs l_timestp_tz_2, compute:
         l_interval := l_timestp_ltz_2 - l_timestp_tz_2
     is OK.
   
     But the Comparison:
         l_timestp_ltz_2 < l_timestp_tz_2
     hits ORA-01878.
     
     If we look l_timestp_ltz_2: 
        l_timestp_ltz_2 = 26*MAR*2017 03:07:00 EUROPE/ZURICH CET
     it is marked with wrong Time Zone Abbreviation "CET" because "26*MAR*2017 03:07:00 EUROPE/ZURICH" has to be with "CEST".
     As we know Time Zone Abbreviation "CET" is defined as TZ Offset "+01:00".
     Probably Oracle is trying to convert "26*MAR*2017 03:07:00 EUROPE/ZURICH" to TZ Offset "+01:00", it results in a non-exist
     datetime "26*MAR*2017 02:07:00 EUROPE/ZURICH", hence ORA-01878.
     
     However, it is not clear why Arithmetic:
        l_timestp_ltz_2 - l_timestp_tz_2
     works.

(3). For l_timestp_tz_2 vs l_timestp_2, same as Point (1).


2. SQL Comparisons


TODO: Comparisons of table column values in SQL Where Clause.


3. SQL and PLSQL Comparisons


TODO: Comparisons between table column values and bind variables in SQL Where Clause.