Note 1: Tested in Oracle 19.13, 19.10, 18.9, 12.1
Note 2: The behaviour was first observed by other people in Oracle applications.
1. Test Setup
We create a test table with a column of data type "timestamp with local time zone" and insert two rows with value of "to_date(1,'J')" (4712-JAN-01 00:00:00 BC) in Named TZ and Offset TZ respectively.
drop table test_tab;
create table test_tab (id number, lts timestamp with local time zone);
alter session set time_zone = 'Europe/Paris';
-- row 1 inserted in Named TZ
insert into test_tab values (1, to_date(1,'J'));
commit;
alter session set time_zone = '+02:00';
-- row 2 inserted in Offset TZ
insert into test_tab values (2, to_date(1,'J'));
commit;
alter session set nls_date_format ='YYYY*MON*DD HH24:MI:SS AD';
alter session set nls_timestamp_format ='YYYY*MON*DD HH24:MI:SS.FF3 AD';
alter session set nls_timestamp_tz_format ='YYYY-MON-DD HH24:MI:SS.FF3 TZR TZD AD';
select validate_conversion('0' as date, 'J', 'NLS_DATE_LANGUAGE = American') not_valid_date_0_return_0,
validate_conversion('1' as date, 'J', 'NLS_DATE_LANGUAGE = American') valid_date_1_return_1,
validate_conversion('2' as date, 'J', 'NLS_DATE_LANGUAGE = American') valid_date_2_return_1
from dual;
NOT_VALID_DATE_0_RETURN_0 VALID_DATE_1_RETURN_1 VALID_DATE_2_RETURN_1
------------------------- --------------------- ---------------------
0 1 1
select cast('0' as date default '2459808' on conversion error, 'J', 'NLS_DATE_LANGUAGE = American') not_valid_date_0_return_today,
cast('1' as date default '2459808' on conversion error, 'J', 'NLS_DATE_LANGUAGE = American') valid_date_1_return,
cast('2' as date default '2459808' on conversion error, 'J', 'NLS_DATE_LANGUAGE = American') valid_date_2_return
from dual;
NOT_VALID_DATE_0_RETURN VALID_DATE_1_RETURN VALID_DATE_2_RETURN
----------------------- ----------------------- -----------------------
2022*JUN*26 00:00:00 AD 4712*JAN*01 00:00:00 BC 4712*JAN*02 00:00:00 BC
2. Test Run
We will make 4 tests for 4 combinations of Named and Offset TZ.
All commented test outputs are from Oracle 19.13 on Sqlplus running on Microsoft Windows remotely connecting to Unix DB.
2.1 Test in Named TZ for Row inserted in Named TZ
col id for 999
col lts for a40
col dtext for a40
alter session set time_zone = 'Europe/Paris';
select dbtimezone, sessiontimezone from dual;
-- +01:00 Europe/Paris
select t.*, dump(lts) dtext from test_tab t where id = 1;
-- 1 7161*JAN*01 00:51:00.000 AD Typ=231 Len=7: 53,88,1,1,1,52,1
select cast(lts as timestamp with local time zone) from test_tab where id = 1;
-- 7161*JAN*01 00:51:00.000 AD
select cast(lts as timestamp with time zone) from test_tab where id = 1;
-- ORA-01866: the datetime class is invalid
select cast(lts as date) from test_tab where id = 1;
-- ORA-01866: the datetime class is invalid
select sys_extract_utc("LTS") from test_tab where id = 1;
-- ORA-01866: the datetime class is invalid
2.2 Test in Offset TZ for Row inserted in Named TZ
alter session set time_zone = '+02:00';
select dbtimezone, sessiontimezone from dual;
-- +01:00 +02:00
select t.*, dump(lts) dtext from test_tab t where id = 1;
-- 1 7161*JAN*01 01:51:00.000 AD Typ=231 Len=7: 53,88,1,1,1,52,1
select cast(lts as timestamp with local time zone) from test_tab where id = 1;
-- 7161*JAN*01 01:51:00.000 AD
select cast(lts as timestamp with time zone) from test_tab where id = 1;
-- 4712-JAN-02 01:51:00.000 +02:00 BC
select cast(lts as date) from test_tab where id = 1;
-- 4712*JAN*01 01:51:00 BC
select sys_extract_utc("LTS") from test_tab where id = 1;
-- 4712*JAN*01 23:51:00.000 BC
2.3 Test in Named TZ for Row inserted in Offset TZ
alter session set time_zone = 'Europe/Paris';
select dbtimezone, sessiontimezone from dual;
-- +01:00 Europe/Paris
select t.*, dump(lts) dtext from test_tab t where id = 2;
-- ORA-01877: string is too long for internal buffer
select cast(lts as timestamp with local time zone) from test_tab where id = 2;
-- ORA-01877: string is too long for internal buffer
select cast(lts as timestamp with time zone) from test_tab where id = 2;
-- ORA-01877: string is too long for internal buffer
select cast(lts as date) from test_tab where id = 2;
-- ORA-01858: a non-numeric character was found where a numeric was expected
select sys_extract_utc("LTS") from test_tab where id = 2;
-- ORA-01877: string is too long for internal buffer
2.4 Test in Offset TZ for Row inserted in Offset TZ
alter session set time_zone = '+02:00';
select dbtimezone, sessiontimezone from dual;
-- +01:00 +02:00
select t.*, dump(lts) dtext from test_tab t where id = 2;
-- 2 2848*MAY*07 00:00:00.000 BC Typ=231 Len=7: 71,152,151,127,24,1,1
select cast(lts as timestamp with local time zone) from test_tab where id = 2;
-- 2848*MAY*07 00:00:00.000 BC
select cast(lts as timestamp with time zone) from test_tab where id = 2;
-- ORA-01877: string is too long for internal buffer
select cast(lts as date) from test_tab where id = 2;
-- ORA-01866: the datetime class is invalid
select sys_extract_utc("LTS") from test_tab where id = 2;
-- ORA-01866: the datetime class is invalid
3. Script to Find to_date(1,'J')
With following script, we can find all rows with column value: "to_date(1,'J')":
alter session set time_zone = '+02:00';
-- alter session set time_zone = dbtimezone;
-- alter session set time_zone = 'Europe/Paris';
declare
l_lts timestamp with local time zone;
l_lts_dump varchar2(50);
begin
for c in (select id from test_tab)
loop
begin
select lts, dump(lts) into l_lts, l_lts_dump from test_tab where id = c.id;
if l_lts_dump like 'Typ=231 Len=7: 53,88,1,1,%' or l_lts_dump like 'Typ=231 Len=7: 71,152,151,127,%' then
dbms_output.put_line('ID='||c.id ||', '||l_lts||','||l_lts_dump||'===>4712-01-01 BC'); --write to a table
else
dbms_output.put_line('ID='||c.id ||', '||l_lts||','||l_lts_dump);
end if;
exception when others
then dbms_output.put_line('ID='||c.id||', '||SQLERRM); --write to a table
end;
end loop;
end;
/
ID=1, 4712*JAN*01 01:51:00.000 BC,Typ=231 Len=7: 53,88,1,1,1,52,1===>4712-01-01 BC
ID=2, ORA-01891: Datetime/Interval internal error
alter session set time_zone = 'Europe/Paris';
select * from test_tab where lts = to_date(1,'J');
-- 1 7161*JAN*01 00:51:00.000 AD
alter session set time_zone = '+02:00';
select * from test_tab where lts = to_date(1,'J');
-- no rows selected
4. Error Code and Date Format
Following tests demonstrate that Error Code varies with Date Format:
alter session set time_zone = 'Europe/Paris';
alter session set nls_date_format ='DD-MON-YYYY';
select cast(lts as date) from test_tab where id = 2;
-- ORA-01801: date format is too long for internal buffer
alter session set nls_date_format ='YYYY-MON-DD';
select cast(lts as date) from test_tab where id = 2;
-- ORA-01858: a non-numeric character was found where a numeric was expected
5. Oracle Releases and Used Tools
The output depends on Oracle Releases and used tools (Sqlplus local or remote Connections, TOAD, Sql Developer).
For example,
--=== Oracle 19.13 with remote Connection:
alter session set time_zone = 'Europe/Paris';
select cast(lts as timestamp with local time zone) from test_tab where id = 1;
-- 7161*JAN*01 00:51:00.000 AD
--=== Oracle 19.10, 18.9 and 12.1 with remote Connection:
alter session set time_zone = 'Europe/Paris';
select cast(lts as timestamp with local time zone) from test_tab where id = 1;
-- ORA-01866: the datetime class is invalid
6. 1866 Trace Event
Oracle MOS: "EM 12c: Error in the Enterprise Manager 12.1.0.4 Cloud Control Repository Database Alert Log: ORA-01866: the datetime class is invalid (Doc ID 1969582.1)" documented 1866 trace event as follows:
Set the event:
alter system set events '1866 trace name errorstack level 3';
wait for the next ORA-1866
alter system set events '1866 trace name errorstack off';
From them, we can locate the problem program, table, data block and table rows.
For example,
alter session set max_dump_file_size = UNLIMITED;
alter system set events '1866 trace name errorstack level 4';
alter session set time_zone = 'Europe/Paris';
declare
l_lts timestamp with local time zone;
l_lts_date date;
l_lts_dump varchar2(50);
begin
select cast(lts as date), dump(lts) into l_lts_date, l_lts_dump from test_tab where id = 1;
-- ORA-01866: the datetime class is invalid
end;
/
alter system set events '1866 trace name errorstack off';
-- Output
ERROR at line 1:
ORA-01866: the datetime class is invalid
ORA-06512: at line 6
2022-06-25T06:42:45.774336+02:00
Errors in file /orabin/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_ora_24592.trc:
ORA-01866: the datetime class is invalid
ORA-01866: the datetime class is invalid
----- Current SQL Statement for this session (sql_id=068wc1kt3fhhn) -----
SELECT CAST(LTS AS DATE), DUMP(LTS) FROM TEST_TAB WHERE ID = 1
----- PL/SQL Call Stack -----
object line object
handle number name
0x80bf09d8 6 anonymous block
----- Call Stack Trace -----
[9] (dbgdProcessEventActions()+525 -> dbgdRunActions())
[10] (dbgdChkEventKgErr()+394 -> dbgdProcessEventActions())
[11] (dbkdChkEventRdbmsErr()+65 -> dbgdChkEventKgErr())
[12] (dbgePostErrorKGE()+1066 -> dbkdChkEventRdbmsErr())
[13] (dbkePostKGE_kgsf()+71 -> dbgePostErrorKGE())
[14] (kgeade()+392 -> dbkePostKGE_kgsf())
[15] (kgeselv()+89 -> kgeade())
[16] (kgesecl0()+145 -> kgeselv())
[17] (evadica()+565 -> kgesecl0())
[18] (evaopn2()+747 -> evadica())
[19] (evaopn2()+594 -> evaopn2())
[20] (evaopn2()+594 -> evaopn2())
[21] (opifcr()+524 -> evaopn2())
[22] (kdstf110010100000000km()+1015 -> opifcr())
[23] (kdsttgr()+2154 -> kdstf110010100000000km())
[24] (qertbFetch()+1090 -> kdsttgr())
[25] (opifch2()+3211 -> qertbFetch())
[26] (opiefn0()+490 -> opifch2())
[27] (opipls()+3142 -> opiefn0())
[28] (opiodr()+1202 -> opipls())
[29] (rpidrus()+198 -> opiodr())
[30] (skgmstack()+65 -> rpidrus())
[31] (rpidru()+132 -> skgmstack())
[32] (rpiswu2()+543 -> rpidru())
[33] (rpidrv()+1266 -> rpiswu2())
[34] (psddr0()+467 -> rpidrv())
[35] (psdnal()+624 -> psddr0())
[36] (pevm_EXECC()+306 -> psdnal())
[37] (pfrinstr_EXECC()+56 -> pevm_EXECC())
[38] (pfrrun_no_tool()+60 -> pfrinstr_EXECC())
[39] (pfrrun()+902 -> pfrrun_no_tool())
[40] (plsql_run()+752 -> pfrrun())
From above tests, we can see the dump of those special datetime:
select id, dump(lts, 16) from test_tab;
ID DUMP(LTS,16)
---- ---------------------------------
1 Typ=231 Len=7: 35,58,1,1,1,34,1
2 Typ=231 Len=7: 47,98,97,7f,18,1,1
BH (0x114f7b180) file#: 74 rdba: 0x001300ff (1024/1245439) class: 1 ba: 0x114404000
set: 15 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,25
dbwrid: 0 obj: 4733309 objn: 4733309 tsn: [0/3315] afn: 74 hint: f
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 7] 35 58 01 01 01 34 01
tab 0, row 1, @0x1f7c
tl: 14 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [ 7] 47 98 97 7f 18 01 01
7. Datatype Conversion
Look xplan:
select * from test_tab where lts = to_date(1,'J');
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 4 | 104 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LTS"=TO_DATE('-4712-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
which is datatype "DATE", whereas LTS is datatype "TIMESTAMP WITH LOCAL TIME ZONE"
(such conversion can cause "ORA-01878: specified field not found in datetime or interval").
Oracle Datetime (1) - Concepts wrote:
When you compare date and timestamp values, Oracle Database converts the data to the more precise data type
before doing the comparison.
For example, if you compare data of TIMESTAMP WITH TIME ZONE data type with data of TIMESTAMP data type,
Oracle Database converts the TIMESTAMP data to TIMESTAMP WITH TIME ZONE, using the session time zone.
The order of precedence for converting date and timestamp data is as follows:
DATE
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE
For more discussions of Oracle datetime, see Blog:
Oracle Datetime (1) - Concepts
