Why is DATE data type treated like TIMESTAMP(0) data type in Oracle mode in H2?

Question

According to the H2 documentation, in the Oracle compatibility mode:

DATE data type is treated like TIMESTAMP(0) data type.

Meantime, DATE and TIMESTAMP(0) datatypes are not the same in Oracle. Compare:

SELECT CAST(SYSDATE AS TIMESTAMP(0)), CAST(SYSDATE AS DATE) from dual

gives

25-MAR-22 13.07.42.000000000 25-MAR-22

respectively.

In particular, this weird treating of DATE as TIMESTAMP(0) influences on how H2 calculates the difference between two dates. Again, in Oracle:

SELECT CAST(TO_DATE('2022-01-05', 'YYYY-MM-DD') AS TIMESTAMP(0)) - CAST(TO_DATE('2022-01-01', 'YYYY-MM-DD') AS TIMESTAMP(0)) from dual

gives

+04 00:00:00.000000

and

SELECT CAST(TO_DATE('2022-01-05', 'YYYY-MM-DD') AS DATE) - CAST(TO_DATE('2022-01-01', 'YYYY-MM-DD') AS DATE) from dual

produces just:

4

Apparently, for H2 both above queries produce the result in nanoseconds and not days as expected.

So, it is an H2 bug or I am missing something?

Answer 1

Meantime, DATE and TIMESTAMP(0) datatypes are not the same in Oracle

Oracle differs from many other RDBMS in that its DATE data type ALWAYS contains both a date and a time component. Its implementation predates the ANSI standard.

In Oracle, if you have the table:

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

and insert the data:

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

Then you can look at the binary data being stored using the DUMP function:

SELECT DUMP(ts) AS dump_ts,
       DUMP(dt) AS dump_dt
FROM   table_name;

Which outputs:

DUMP_TS	DUMP_DT
Typ=180 Len=7: 120,122,3,25,15,13,37	Typ=12 Len=7: 120,122,3,25,15,13,37

Then you can see that they are both stored as 7-byte binary values:

• 120 = Century + 100
• 122 = Year-of-century + 100
• 3 = Month
• 25 = Day
• 15 = Hour + 1
• 13 = Minutes + 1
• 37 = Seconds + 1

And the binary values are identical (the only difference is in the meta-data Typ where 180 = TIMESTAMP and 12 = DATE).

Effectively, they are stored identically.

db<>fiddle here

---

However

The side-effects of a TIMESTAMP vs. a DATE data type in Oracle may lead to different effects.

• When you subtract a TIMESTAMP and either a TIMESTAMP or a DATE then the return value is an INTERVAL DAY TO SECOND data type.

  When you subtract a DATE and a DATE then the default return value is a NUMBER representing the number of days difference.

• When you display a TIMESTAMP then the client application you are using may default to using the NLS_TIMESTAMP_FORMAT session parameter to format the timestamp as a string and the default for this parameter will typically show date, time and fractional seconds.

  When you display a DATE then the client application you are using may default to using the NLS_DATE_FORMAT session parameter to format the date as a string and the default for this parameter will show date but not time (and there will never be any fractional seconds to show). Just because the client application may chose not to show the time component does not mean that the time component does not exist.

  If you set the session parameters using:

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

  Then, provided your client application is using those parameters to format them, they will display identically.

The problem you are seeing with the difference in Oracle is due to these side effects.

Answer 2

If the question is

So, it is an H2 bug or I am missing something?

than the the answer would be: No, it is not a bug, and what you've missed is the fact, that compatibility modes in H2 are just that - attempt to reach with minimal efforts maximum compatibility with different databases. H2 is not an emulator for any non-standard features (quirks) of those databases. It that particular case, to achieve identical behaviour would require to introduce new non-standard data type, which goes beyond "minimal effort" level.

Answer 3

The different in the output of the values in the first query is down to the session's NLS settings. These control the display format for dates and timestamps:

sho parameter nls_date_format

NAME            TYPE   VALUE       
--------------- ------ ----------- 
nls_date_format string DD-MON-YYYY 

sho parameter nls_timestamp_format

NAME                 TYPE   VALUE                     
-------------------- ------ ------------------------- 
nls_timestamp_format string DD-MON-YYYY HH24.MI.SSXFF 

SELECT CAST(SYSDATE AS TIMESTAMP(0)), CAST(SYSDATE AS DATE) from dual;

CAST(SYSDATEASTIMESTAMP(0))    CAST(SYSDAT
------------------------------ -----------
25-MAR-2022 12.18.24.000000000 25-MAR-2022

If you change these to be the same format, both expressions return the same result:

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT CAST(SYSDATE AS TIMESTAMP(0)), CAST(SYSDATE AS DATE) from dual;

CAST(SYSDATEASTIMEST CAST(SYSDATEASDATE) 
-------------------- --------------------
25-MAR-2022 12:17:43 25-MAR-2022 12:17:43

So they both contain the full date + time with no fractional seconds.

Note that while date and timestamp(0) have the same precision, as your further examples show they work differently:

• Subtracting one date from another returns the number of days between the values as a number
• Subtracting a timestamp from a date or timestamp returns an interval

So the result of:

SELECT CAST(TO_DATE('2022-01-05',  'YYYY-MM-DD') AS DATE) - CAST(TO_DATE('2022-01-01',  'YYYY-MM-DD') AS DATE) from dual

Is 4 days.