CODEWITHSUNDEEP
oracle-databasehsqldb
Zeemee
Zeemee

Reputation: 10714

Why does CURRENT_DATE contains time in Oracle Mode?

When I connect to

jdbc:hsqldb:mem:lbw;sql.syntax_ora=true

the statement

SELECT CURRENT_DATE FROM dual

results in

2014-01-31 10:35:54

This is in opposite to connections without Oracle syntax mode, where CURRENT_DATE doesn't contain time. As described in the HSQLDB documentation, DATE is interpreted as TIMESTAMP(0) in Oracle syntax mode. But in Oracle 10g itself, CURRENT_DATE behaves as expected (without time).

This difference seems to include DATE fields in general.

Why does HSQLDB behave this way?
Is there a way to disable the automatic conversion?

Upvotes: 0

Views: 266

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

From the same HSQLDB documentation you linked to:

Datetime types

HSQLDB fully supports datetime and interval types and operations, including all relevant optional features, as specified by the SQL Standard since SQL-92. The two groups of types are complementary.

The DATE type represents a calendar date with YEAR, MONTH and DAY fields.

The TIME type represents time of day with HOUR, MINUTE and SECOND fields, plus an optional SECOND FRACTION field.

The TIMESTAMP type represents the combination of DATE and TIME types.

The Oracle compatibility section says:

  • The DATE type is interpreted as TIMESTAMP(0) in ORA syntax mode.

Oracle's DATE data type "contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND". So it's equivalent to an HSQLDB TIMESTAMP(0) data type, and in Oracle mode it is treated as such.

Oracle dates always have a time component, even if it is all zeros for midnight. If your SQL client doesn't show it by default you can see that with select to_char(current_date, 'YYYY-MM-DD HH24:MI:SS'), as others have already pointed out.

In normal non-Oracle mode HSQLDB is just treating the value as an SQL-standard DATE and dropping the time portion; in Oracle mode it preserves the time. There doesn't seem to be any way to selectively enable some aspects of the Oracle mode, so you're stuck with the time - really not sure why that is an issue though since it's just reflecting the data you have in your database. If you want to ignore the time you could always select trunc(current_date), which will take the time back to midnight; but it will still show as 2014-01-31 00:00:00 because it's still going to be treated as TIMESTAMP(0).

Upvotes: 3

Related Questions