At first glance, Oracle Datetime is obvious, but in reality it is a vulnerable part of SQL and PLSQL.
Oracle Datetime most frequently cited master Document:
MOS: 340512.1 Timestamps & time zones – Frequently Asked Questions
contains a total of 34 Items, of which 2 have non obvious deficiencies.
We will take 4 examples to demonstrate the deep implication to applications. The first 2 examples are those 2 Items of MOS: 340512.1, the other 2 examples reveal the cryptic behaviors of Datetime.
1. Example-1: Datetime Conversion
In MOS: 340512.1, Item 22) wrote:
22) How can I compute the difference between two timestamp values?
You can simply subtract 2 timestamps from each other to get a interval, for example this calculates
how long it is since/before lunch on Christmas day:
declare
duration interval day(6) to second(6);
v_start timestamp := to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF');
v_end timestamp(6) := sysTimestamp;
begin
duration := v_end - v_start;
dbms_output.put_line ('Now: '||to_char(v_end,'DD-MON-YYYY HH24:MI:SSxFF'));
dbms_output.put_line ('Difference: '|| to_char(duration));
end;
/
And it works the same in sql:
create table temp(start_TS Timestamp(6), duration interval day(6) to second(6) );
Insert into temp values
(to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF'),
(sysTimeStamp - to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF')));
SQL> alter session set time_zone = '+00:00';
SQL> select SYSTIMESTAMP, SESSIONTIMEZONE from dual;
SYSTIMESTAMP SESSIONTIMEZONE
----------------------------------- ---------------
2017-MAY-02 08:15:14 +02:00 +00:00
SQL> run Item 22) PLSQL anonymous Block
Now: 02-MAY-2017 08:19:19.662437000
Difference: +004876 18:19:19.662437
SQL> select * from temp;
START_TS DURATION
--------------------- -----------------------
2003*DEC*25 14:00:00 +004876 16:19:28.701151
And it works the same in sql
Let's look what caused such a discrepancy. In PLSQL anonymous Block, when
v_end timestamp(6) := sysTimestamp;
only datetime part of sysTimestamp is verbatim copied to v_end by ignoring Offset TZ.
So the real computation is like:
v_end = '02-MAY-2017 08:19:19'
v_start = '25-DEC-2003 14:00:00'
Difference = v_end - v_start
= select to_timestamp('02-MAY-2017 08:19:19', 'DD-MON-YYYY HH24:MI:SS')
- to_timestamp('25-DEC-2003 14:00:00', 'DD-MON-YYYY HH24:MI:SS')
from dual;
= '+004876 18:19:19'
Whereas in SQL, when(sysTimeStamp - to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF'))
the second part, which has no Offset TZ, is accomplished with default SESSIONTIMEZONE = '+00:00'.
So the effective computation is like:
DURATION = '02-MAY-2017 08:19:28 +02:00' - '25-DEC-2003 14:00:00 +00:00'
= '02-MAY-2017 08:19:28 +02:00' - '25-DEC-2003 16:00:00 +02:00'
= select to_timestamp_tz('02-MAY-2017 08:19:28 +02:00', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')
- to_timestamp_tz('25-DEC-2003 16:00:00 +02:00', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')
from dual;
= '+4876 16:19:28'
Because Server TZ is 2 hours earlier than Session TZ, "Difference" is about 2 hours more than "DURATION".
2. Example-2: Datetime Arithmetic Data Type
In MOS: 340512.1, Item 28) said:
28) Why does using Datetime Arithmetic on datatypes having timezone information seams to give incorrect result ?
It's often not known that using Datetime Arithmetic in most cases returns a DATE dataype (Typo datatype).
Now we add the similar dump in PLSQL code, and make a test:
--------------- Test Code ---------------
ALTER SESSION SET NLS_DATE_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD'
ALTER SESSION SET TIME_ZONE = '+02:00';
set serveroutput on
declare
v_t1 timestamp with time zone;
v_t2 timestamp with time zone;
v_date date := sysdate;
l_dump varchar2(50);
begin
v_t1 := from_tz(cast(to_date('2013-11-04', 'YYYY-MM-DD') as timestamp), '-06:00');
dbms_output.put_line(v_t1);
select (dump(v_t1, 1016)) into l_dump from dual;
dbms_output.put_line('v_t1 DUMP : '||l_dump);
v_t2 := v_t1 + 35/1440;
dbms_output.put_line(v_t2);
select (dump(v_t2, 1016)) into l_dump from dual;
dbms_output.put_line('v_t2 Number DUMP : '||l_dump);
v_t2 := v_t1 + NUMTODSINTERVAL(35, 'MINUTE');
dbms_output.put_line(v_t2);
select (dump(v_t2, 1016)) into l_dump from dual;
dbms_output.put_line('v_t2 Interval DUMP : '||l_dump);
dbms_output.put_line(v_date);
select (dump(v_date, 1016)) into l_dump from dual;
dbms_output.put_line('v_date DUMP : '||l_dump);
end;
/
--------------- Test Output ---------------
2013-NOV-04 00:00:00 -06:00
v_t1 DUMP : Typ=181 Len=13: 78,71,b,4,7,1,1,0,0,0,0,e,3c
2013-NOV-04 00:35:00 +02:00
v_t2 Number DUMP : Typ=181 Len=13: 78,71,b,3,17,24,1,0,0,0,0,16,3c
2013-NOV-04 00:35:00 -06:00
v_t2 Interval DUMP : Typ=181 Len=13: 78,71,b,4,7,24,1,0,0,0,0,e,3c
03*05*2017 07:10:29
v_date DUMP : Typ=12 Len=7: 78,75,5,3,8,b,1e
It's often not known that using Datetime Arithmetic in most cases returns a DATE dataype.
probably only holds for SQL, not applicable for PLSQL.
3. Example-3: Datetime Comparisons
Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.
--------------- Test Code ---------------
alter session set time_zone = 'Europe/London';
declare
l_timestp_tz TIMESTAMP WITH TIME ZONE;
l_timestp_ltz TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_ltz_15 TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_ltz_75 TIMESTAMP WITH LOCAL TIME ZONE;
l_diff INTERVAL DAY TO SECOND;
l_dump varchar2(100);
begin
l_timestp_tz := to_timestamp_tz('2017-03-26 01:52:00 Europe/Zurich CET', 'yyyy-mm-dd hh24:mi:ss tzr tzd');
l_timestp_ltz := l_timestp_tz;
l_timestp_ltz_15 := l_timestp_ltz + numtodsinterval(15, 'Minute'); -- after 15 minutes
l_timestp_ltz_75 := l_timestp_ltz + numtodsinterval(75, 'Minute'); -- after 75 minutes
dbms_output.put_line('l_timestp_tz = '||l_timestp_tz);
dbms_output.put_line('l_timestp_ltz = '||l_timestp_ltz);
dbms_output.put_line('l_timestp_ltz_15 = '||l_timestp_ltz_15);
dbms_output.put_line('l_timestp_ltz_75 = '||l_timestp_ltz_75);
select (dump(l_timestp_ltz_15, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_15 Dump = '||l_dump); -- after 15 minutes
select (dump(l_timestp_ltz_75, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_75 Dump = '||l_dump); -- after 75 minutes
l_diff := l_timestp_ltz_15 - l_timestp_tz;
dbms_output.put_line('Diff = ' ||l_diff);
-- Diff works
if l_diff > numtodsinterval(0, 'Minute') and (extract(minute from l_diff)) > 0 then
dbms_output.put_line('Diff Compare > 0');
else
dbms_output.put_line('Diff Compare <= 0');
end if;
-- Comparison does NOT works
if l_timestp_ltz_15 <= l_timestp_tz then -- line 35
dbms_output.put_line('l_timestp_ltz_15 Lost');
else
dbms_output.put_line('l_timestp_ltz_15 Win');
end if;
end;
/
--------------- Test Output ---------------
l_timestp_tz = 2017-MAR-26 01:52:00 EUROPE/ZURICH CET
l_timestp_ltz = 2017*MAR*26 00:52:00
l_timestp_ltz_15 = 2017*MAR*26 02:07:00
l_timestp_ltz_75 = 2017*MAR*26 02:07:00
l_timestp_ltz_15 Dump = Typ=231 Len=7: 78,75,3,1a,3,8,1
l_timestp_ltz_75 Dump = Typ=231 Len=7: 78,75,3,1a,3,8,1
Diff = +00 00:15:00.000000
Diff Compare > 0
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 35
When we perform "l_timestp_ltz_15 <= l_timestp_tz", we get ORA-01878. Because both operands have different Data Types, Oracle implicitly promotes "TIMESTAMP WITH LOCAL TIME ZONE" to "TIMESTAMP WITH TIME ZONE". This is documented in:
Oracle Datetime Data Types and Time Zone Support - Datetime Comparisons wrote:
When you compare date and timestamp values, Oracle Database converts the data to the more precise data type before doing the comparison. The order of precedence for converting date and timestamp data is as follows:
1. DATE 2. TIMESTAMP 3. TIMESTAMP WITH LOCAL TIME ZONE 4. TIMESTAMP WITH TIME ZONEIt seems that Oracle takes value of l_timestp_ltz_15, which is "2017*MAR*26 02:07:00", and converts it to the data type of second operand l_timestp_tz, which is "TIMESTAMP WITH TIME ZONE" and more precise than "TIMESTAMP WITH LOCAL TIME ZONE". Since there does not exist "2017*MAR*26 02:07:00 EUROPE/ZURICH", it throws ORA-01878. It is very strange that the converting neglects the Time Zone info of l_timestp_ltz_15, and only literally picks its date and time. Probably internally l_timestp_ltz_15 is implemented as a datatype TIMESTAMP.
If we use another equivalence of relational comparison, at first compute:
l_diff := l_timestp_ltz_15 - l_timestp_tz;
and then compare, the diff value is correctly and comparison works properly.
It is not clear where such a behavior comes from.
Progressive Convertion
As discussed, when comparing different data type of datatime, lower type has to be promoted to higher type. It seems that this convertion is strictly stepwise progressive.
During these Progressive Convertions, it can also throws "ORA-01878". In the following example, we want to compare TIMESTAMP with TIMESTAMP WITH TIME ZONE:
first step is to convert TIMESTAMP "2017*MAR*26 02:07:00" to TIMESTAMP WITH LOCAL TIME ZONE;
second step is to TIMESTAMP WITH TIME ZONE.
However in the first step, TIMESTAMP WITH LOCAL TIME ZONE "2017*MAR*26 02:07:00" is not existed in TZ "Europe/Zurich". If we run the same code in TZ "Europe/London", it is OK because "2017*MAR*26 02:07:00" exists in TZ "Europe/London".
--------------- Test Code ---------------
declare
l_timestp_tz TIMESTAMP WITH TIME ZONE;
l_timestp_start TIMESTAMP;
l_timestp_next TIMESTAMP;
l_timestp_next_ltz TIMESTAMP WITH LOCAL TIME ZONE;
l_timestp_next_tz TIMESTAMP WITH TIME ZONE;
begin
l_timestp_tz := to_timestamp_tz('2017-03-26 01:52:00 +01:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm');
l_timestp_start := l_timestp_tz;
l_timestp_next := l_timestp_start + numtodsinterval(15, 'Minute');
dbms_output.put_line('l_timestp_tz = ' || l_timestp_tz);
dbms_output.put_line('l_timestp_start = ' || l_timestp_start);
dbms_output.put_line('l_timestp_next = ' || l_timestp_next);
-- Code to see strictly stepwise progressive Convertion.
begin
l_timestp_next_ltz := l_timestp_next;
dbms_output.put_line('l_timestp_next_ltz = ' || l_timestp_next_ltz);
l_timestp_next_tz := l_timestp_next_ltz;
dbms_output.put_line('l_timestp_next_tz = ' || l_timestp_next_tz);
exception when others then dbms_output.put_line('--- ORA-01878 in Convertion ---');
end;
-- Compare TIMESTAMP with TIMESTAMP WITH TIME ZONE
if l_timestp_next <= l_timestp_tz then
dbms_output.put_line('l_timestp_next <= l_timestp_tz: True');
else
dbms_output.put_line('l_timestp_next <= l_timestp_tz: False');
end if;
end;
/
--------------- Test Output: Europe/Zurich ---------------
alter session set time_zone = 'Europe/Zurich';
l_timestp_tz = 2017-MAR-26 01:52:00 +01:00
l_timestp_start = 2017*MAR*26 01:52:00
l_timestp_next = 2017*MAR*26 02:07:00
--- ORA-01878 in Convertion ---
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 28
--------------- Test Output: Europe/London ---------------
alter session set time_zone = 'Europe/London';
l_timestp_tz = 2017-MAR-26 01:52:00 +01:00
l_timestp_start = 2017*MAR*26 01:52:00
l_timestp_next = 2017*MAR*26 02:07:00
l_timestp_next_ltz = 2017*MAR*26 02:07:00
l_timestp_next_tz = 2017-MAR-26 02:07:00 EUROPE/LONDON BST
l_timestp_next <= l_timestp_tz: False
4. Example-4: No Index, No ORA-01878
In the following test code, if table is empty, "TABLE ACCESS FULL" has no error because where Clause is used as filter predicate on an empty result set; whereas "INDEX RANGE SCAN" hits ORA-01878 because where Clause is used as access predicate.
If table is not empty, both executions have ORA-01878.
The error is not obvious if test cases are not fully covered.
Datatime constant is constructed by TIMESTAMP WITH TIME ZONE minus a number. (See Blog: Oracle Datetime (5) - SQL Arithmetic Section: Number and Interval Arithmetic)
--------------- Test Code ---------------
drop table ltz_test_tab;
create table ltz_test_tab (loc_ltz TIMESTAMP WITH LOCAL TIME ZONE);
create index ltz_test_tab_idx on ltz_test_tab(loc_ltz);
insert into ltz_test_tab values (localtimestamp);
commit;
truncate table ltz_test_tab;
alter session set time_zone = 'Europe/Paris';
select /*+ full(t) */ count(*) from ltz_test_tab t
where t.loc_ltz < to_timestamp_tz('2017-03-27 02:52:00 +02:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') -1;
select /*+ index(t ltz_test_tab_idx) */ count(*) from ltz_test_tab t
where t.loc_ltz < to_timestamp_tz('2017-03-27 02:52:00 +02:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') -1;
--------------- Test Output ---------------
SQL > select /*+ full(t) */ count(*) from ltz_test_tab t
where t.loc_ltz < to_timestamp_tz('2017-03-27 02:52:00 +02:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') -1;
COUNT(*)
----------
0
SQL > select /*+ index(t ltz_test_tab_idx) */ count(*) from ltz_test_tab t
where t.loc_ltz < to_timestamp_tz('2017-03-27 02:52:00 +02:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') -1;
ORA-01878: specified field not found in datetime or interval
5. Example-5: Datetime Interval Irregularity
Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.
Run code below and look its output:
--------------- Test Code ---------------
alter session set time_zone = 'Europe/Zurich';
declare
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_interval_b INTERVAL DAY TO SECOND;
l_dump varchar2(100);
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;
-- TIMESTAMP Interval
l_interval := l_timestp_a2 - l_timestp_a1;
dbms_output.put_line('TIMESTAMP Interval = '||l_interval);
dbms_output.put_line('l_timestp_ltz_a1 = '||to_char(l_timestp_ltz_a1, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
dbms_output.put_line('l_timestp_ltz_b1 = '||to_char(l_timestp_ltz_b1, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_a1, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_a1 DUMP = '||l_dump);
select (dump(l_timestp_ltz_b1, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_b1 DUMP = '||l_dump);
dbms_output.put_line('l_timestp_ltz_a2 = '||to_char(l_timestp_ltz_a2, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
dbms_output.put_line('l_timestp_ltz_b2 = '||to_char(l_timestp_ltz_b2, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
select (dump(l_timestp_ltz_a2, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_a2 DUMP = '||l_dump);
select (dump(l_timestp_ltz_b2, 1016)) into l_dump from dual;
dbms_output.put_line('l_timestp_ltz_b2 DUMP = '||l_dump);
-- LOCAL TIME ZONE Interval
l_interval := l_timestp_ltz_a2 - l_timestp_ltz_a1;
dbms_output.put_line('LOCAL TZ Interval_a = '||l_interval);
-- LOCAL TIME ZONE Interval_X
l_interval_b := l_timestp_ltz_b2 - l_timestp_ltz_b1;
dbms_output.put_line('LOCAL TZ Interval_b = '||l_interval_b);
end;
/
--------------- Test Output ---------------
TIMESTAMP Interval = +00 02:03:00.000000
l_timestp_ltz_a1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
l_timestp_ltz_b1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
l_timestp_ltz_a1 DUMP = Typ=231 Len=7: 78,75,3,1a,2,35,1
l_timestp_ltz_b1 DUMP = Typ=231 Len=7: 78,75,3,1a,2,35,1
l_timestp_ltz_a2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
l_timestp_ltz_b2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
l_timestp_ltz_a2 DUMP = Typ=231 Len=7: 78,75,3,1a,3,38,1
l_timestp_ltz_b2 DUMP = Typ=231 Len=7: 78,75,3,1a,3,38,1
LOCAL TZ Interval_a = +00 02:03:00.000000
LOCAL TZ Interval_b = +00 01:03:00.000000
Their formatted output and dump attest:
l_timestp_ltz_a1 = l_timestp_ltz_b1 l_timestp_ltz_a2 = l_timestp_ltz_b2But
l_timestp_ltz_a2 - l_timestp_ltz_a1 = 02:03 l_timestp_ltz_b2 - l_timestp_ltz_b1 = 01:03
There is no obvious clue where the cryptic disparity comes from.
In math, if:
a1 = b1 a2 = b2then
a2 - a1 = b2 - b1But this PLSQL code seems not able to comply to the basic math law.
