Oracle: Difference between DATE and TIMESTAMP(0)

Question

What is the difference, if any, between DATE and TIMESTAMP(0) in Oracle? Both data types occupy 6 bytes, and contain date with time, but without fractional seconds and a timezone.

The TIMESTAMP was added to Oracle about 20 years after DATE to comply with ANSI standard. TIMESTAMP or TIMESTAMP(6) can hold fractions of seconds, so it is different from DATE, but is there any difference between TIMESTAMP(0) and DATE?

Answer 1

One is a DATE data type and one is a TIMESTAMP data type.

They both take up 7 bytes (century, year-of-century, month, day, hour, minute and second).

The DATE will be implicitly formatted by the NLS_DATE_FORMAT session parameter when it is displayed.

The TIMESTAMP will be implicitly formatted by the NLS_TIMESTAMP_FORMAT session parameter when it is displayed.

For example:

CREATE TABLE table_name (
  dt DATE,
  ts TIMESTAMP(0)
);

INSERT INTO table_name ( dt, ts ) VALUES ( SYSDATE, SYSTIMESTAMP );

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF6';

Then:

SELECT dt,
       DUMP( dt ),
       ts,
       DUMP( ts )
FROM   table_name;

Outputs:

DT                  | DUMP(DT)                            | TS                         | DUMP(TS)                            
:------------------ | :---------------------------------- | :------------------------- | :-----------------------------------
2020-10-08T14:21:35 | Typ=12 Len=7: 120,120,10,8,15,22,36 | 2020-10-08T14:21:36.000000 | Typ=180 Len=7: 120,120,10,8,15,22,37

Functions that require a TIMESTAMP input won't perform an implicit CAST from a DATE to a TIMESTAMP.

So:

SELECT FROM_TZ( dt, 'UTC' ) FROM table_name;
SELECT FROM_TZ( CAST( dt AS TIMESTAMP(0) ), 'UTC' ) FROM table_name;
SELECT FROM_TZ( ts, 'UTC' ) FROM table_name;

The first statement raises an ORA-00932: inconsistent datatypes: expected TIMESTAMP got DATE exception but the second and third works.

As mentioned by @WernfriedDomscheit in comments, arithmetic is also different between DATE and TIMESTAMP data types. For example:

SELECT dt - dt,
       ( dt - dt ) DAY TO SECOND,
       ts - ts
FROM   table_name

Outputs:

DT-DT | (DT-DT)DAYTOSECOND  | TS-TS              
----: | :------------------ | :------------------
    0 | +00 00:00:00.000000 | +000000000 00:00:00

The top expression is DATE - DATE and the result is the difference in days (expressed as a NUMBER data type). However, the bottom expression is TIMESTAMP - TIMESTAMP and the result is an INTERVAL DAY TO SECOND data type. It is possible to get DATE subtraction to output an INTERVAL DAY TO SECOND data type but, as shown in the middle example, you need to explicitly state that that is the output you require.

Addition to a DATE or TIMESTAMP also has differences. For example:

SELECT dt + 1,
       dt + INTERVAL '1' DAY,
       ts + 1,
       ts + INTERVAL '1' DAY
FROM   table_name

Outputs:

DT+1                | DT+INTERVAL'1'DAY   | TS+1                | TS+INTERVAL'1'DAY         
:------------------ | :------------------ | :------------------ | :-------------------------
2020-10-09T19:52:21 | 2020-10-09T19:52:21 | 2020-10-09T19:52:22 | 2020-10-09T19:52:22.000000

Adding a number or an INTERVAL to both a DATE and a TIMESTAMP is both syntactically correct (but may not be as expected). If you look you will see that the output for adding a number or an interval to a date gives the same output in both cases. However, there is a difference between the output when adding a number to a timestamp compared to adding an interval to a timestamp; this is because you can only add numbers to date values and by adding a number to a timestamp then Oracle has performed an implicit cast from timestamp down to a date so:

SELECT ts + 1 FROM table_name

is effectively performing:

SELECT CAST(ts AS DATE) + 1 FROM table_name

So, if you want to perform arithmetic with a TIMESTAMP data type then use INTERVAL data types rather than numbers.

db<>fiddle here