I'm stuck.
Oracle shows weird behaviour when handling timestamps, let me explain:
I have a simple table with primary key and an index. AUDIT_FROM_TS is a part of a primary key. It's partitioned using AUDIT_FROM_TS on a monthly interval.
Relevant DDL
CREATE TABLE "SDR"."TRADE_DEAL_F"(
...
"AUDIT_FROM_TS" TIMESTAMP (9) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
...
CONSTRAINT "PK_TRADE_DEAL" PRIMARY KEY ("TRADE_DEAL_ID", "VALID_FROM_DT", "AUDIT_FROM_TS")
...
PARTITION BY RANGE ("AUDIT_FROM_TS") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
...
When running this query:
select count(*) from trade_deal_f where AUDIT_FROM_TS < timestamp '9999-12-31 00:00:00';
I get
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
But this one works just fine:
select count(*) from trade_deal_f where AUDIT_FROM_TS < timestamp '9999-12-15 00:00:00';
I've done a little debugging and if increment the date to the 16th of December 9999, same error is thrown.
Now more debugging...
SELECT DBTIMEZONE from dual;
returns +00:00
SELECT SESSIONTIMEZONE FROM dual;
returns Europe/London
Can anyone help with this please ? I am not 100% sure it's the timezone issue, as it would be offsetting the date by 2 weeks...
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 Europe/London';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 GMT';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 UTC';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 +00:00';
all these seem to be valid...