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
(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.
