Oracle Interval 1 day

I would like to understand the difference between

someDate >= (CURRENT_TIMESTAMP - INTERVAL '1' DAY) 

and

someDate >= (CURRENT_TIMESTAMP - 1)

Because for me it seems the same, but 2nd one seems to have better perf.

1 Answer

The primary difference is that CURRENT_TIMESTAMP - 1 relies on Oracle's ability to perform arithmetic on DATE data type and derive a new date, which is a capability (some) other databases do not support.

Whereas CURRENT_TIMESTAMP - INTERVAL '1' DAY uses an interval data type to achieve the same end. This is standard syntax and widely supported.

I would expect any perceived performance benefit to one over the other to be an illusion, perhaps caused by the "warm cache" effect.

There is a secondary difference. The use of arithmetic in the second case causes an implicit data conversion and the derived value is a DATE whereas the use of INTERVAL means that the derived value of the first example remains a TIMESTAMP.

select dump(current_date) as dt ,dump(current_timestamp) as ts ,dump(current_timestamp-1) as ts_arith ,dump(current_timestamp - interval '1' day) as ts_invl ,dump(current_date - interval '1' day) as dt_invl
from dual
/

This could lead lead to a difference in performance, if SOME_DATE is a date type and the column is indexed. In that case comparing to a date might use the index whereas comparing to a timestamp would not.