exception during spring time change(DST) in where clause

Question

I have a table which has column with data type as "TIMESTAMP(6) WITH LOCAL TIME ZONE"

Problem: For the below query I get exception which says time zones does not exists.

select * from table where update_time <= CURRENT_TIMESTAMP-3/24;

Exception occurs during one hour window of spring time change. How do I overcome this?

Answer 1

I can't reproduce on my environment, but I think the error is caused by the casting of a DATE datatype to an invalid TIMESTAMP WITH LOCAL TIME ZONE.

For instance in the Europe/Paris time zone, the timestamp 2014-03-30 02:30:00 is invalid:

SQL> SELECT to_timestamp_tz('2014-03-30 02:30 Europe/Paris',
  2                         'yyyy-mm-dd hh24:mi TZR')
  3    FROM dual;
SELECT to_timestamp_tz('2014-03-30 02:30 Europe/Paris',
                       *
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

This time and day does not exist since the clocks are forwarded between 02:00 and 03:00.

Since your right hand side of the <= operator is a date (adding a timestamp and a number produces a date) and you compare it to a TIMESTAMP WITH LOCAL TIME ZONE, it gets casted to the datatype of the column.

I think using interval arithmetics will solve the issue since this will be converted to a valid timestamp. As an added benefit, the resulting code will be easier to read.

Can you try:

select * from table where update_time <= CURRENT_TIMESTAMP - INTERVAL '3' HOUR;