CODEWITHSUNDEEP
hsqldb
Betlista
Betlista

Reputation: 10549

HSQLDB just time value in insert

We are using HSQLDB for JUnits, our production database is Oracle.

I have a table in which there is a field (type is timestamp in Oracle) that carries the information about some important time in day.

I wanted to prepare test data for JUnit in script, but I failed.

The basic idea I can do in Oracle DB is

SYSDATE - TRUNC(SYSDATE)

when I tried the same with CURRENT_TIME in HSQLDB I got error:

org.hsqldb.HsqlException: incompatible data type in conversion

the next idea I got was to add time to "empty date", tried:

DATE '0000-01-01'

but got

org.hsqldb.HsqlException: data exception: invalid datetime format

strange for DATE '2013-01-01' it works fine, maybe the initialia zeros are the problem...no they are not, same for DATE '0-01-01'...

In this phase I was kind of mad already (think I can do in Oracle in a second and I spent several hours here).

From documentation I tried to find some combination of functions that creates the required result, but unfortunately I found that the documentation is not clear. It seems so for first read, but when tried I was surprised why this

DATEADD( 'hour', 1, CURRENT_DATE )

works fine, while

DATEADD( 'hour', 1, CURRENT_TIME)

ends with 

org.hsqldb.HsqlException: incompatible data type in conversion

as opposite, DATE_ADD works for both and parameter type for both functions is the same in documentation :-/

I believe that I can do, something like

// 4x call of DATEADD, datetime not working as described above
date - years - months - days + seconds_from_midnight

but there have to be something simple to use (I hope so).

HSQLDB version: 2.3.1

Upvotes: 0

Views: 3499

Answers (1)

fredt
fredt

Reputation: 24372

Use this setting for your tests:

SET DATABASE SQL SYNTAX ORA TRUE

Then this works and returns an INTERVAL

SYSDATE - TRUNC(SYSDATE)

The simpler form is CAST (SYSDATE AS TIME) to return the time part of SYSDATE, which you can convert to an interval.

This date DATE '0000-01-01' is not accepted as there is no YEAR 0. You need to use DATE '0001-01-01'

This works fine to convert the time of the day into an INTERVAL:

LOCALTIME - TIME'00:00:00'

Note LOCALTIME has no time zone, while CURRENT_TIME has.

Upvotes: 2

Related Questions