CODEWITHSUNDEEP
hsqldb
caRameL
caRameL

Reputation: 633

HSQL Get Date and Time from TIMESTAMP

I have a HSQLDB table with some informations and where a timestamp is stored

I want to select informations from specific time periods like every information of october between 0am and 5pm.

I didn't found any function like mysql'ones DATE(myfield) OR TIME(myfield) which would be perfect for what i want.

SELECT * FROM myTable 
WHERE DATE(myField) BETWEEN '2012-10-01' AND '2012-10-30' 
AND TIME(myField) BETWEEN '00:00:00' AND '17:00:00'

The only things i found are to use the functions YEAR(), DAYOFMONTH(), DAYOFYEAR(), HOUR() ANd MINUTE() but I was wondering if there aren't any other simpler solutions.

Upvotes: 2

Views: 9091

Answers (1)

fredt
fredt

Reputation: 24372

There is no need for such specialised functions as HSQLDB supports the general CAST expression for this purpose

CAST(myField AS DATE)
CAST(myField AS TIME)

HSQLDB also supports automatic cast from DATE to TIMESTAMP. Therefore an optimised query that can use an index on the myField column should look like this:

SELECT * FROM myTable 
WHERE myField BETWEEN DATE'2012-10-01' AND DATE'2012-10-30' 
AND CAST(myField AS TIME) BETWEEN TIME'00:00:00' AND TIME'17:00:00'

Upvotes: 5

Related Questions