Tag Archives: timestamp

date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
         X D
---------- -------------------
           2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS')
         X D
---------- -------------------
           2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
   2    1      INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.

SQL> select * from lsc_t where 
  d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

         X D
---------- -------------------
           2000-01-01 06:00:00

Converting to date would deliver wrong result.