Difference between two dates in HSQLDB

Question

Got a database in Libreoffice Base using HSQLDB, in which table Expirations has columns Item and Expiry (date value). I would like to run a query that counts the number of days between ExpiryDate and the current date, outputting Item and Days Remaining expressed as whole days.

As I'm new to SQL, I'm not surprised that my first few attempts have given me syntax errors. Hopefully someone can point out where I went wrong:

SELECT DATEDIFF (DAY, CURRENT_DATE, "Expiry") AS "Days Remaining" FROM "Expirations"

I think it has something to do with first having to select all the entries in the table. Perhaps a SELECT *? If so, not sure how to link it to the DATEDIFF segment. SELECT * WHERE DATEDIFF... also throws up a syntax error.

I think we need to know what the syntax error is. If the column names you have put into quotes are not the actual case of the created object it will complain that it can't find the column or that you don't have priveleges - you must use the correct case if you put it in double quotes: http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#N1048B — Jerry Jeremiah, Sep 05 '22 at 23:35
Is this an embedded or split database setup? If split, then what is the HSQLDB version? — Jim K, Sep 06 '22 at 21:16

score 1 · Answer 1 · answered Sep 05 '22 at 23:46

Ok, so https://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html says:

DATEDIFF ( <field>, <datetime value expr 1>, <datetime value expr 2> )
<field> ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond'
<field> ::= YY | YEAR | MM | MONTH | DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND

Which implies that you can use DAY or 'day' interchangeably - but it doesn't work that way for me - the only one that works for me is 'day':

sql> SELECT DATEDIFF(DAY, CURRENT_DATE, TIMESTAMP '2022-09-22 20:30:40') FROM (VALUES(0));
SEVERE  SQL Error at '<stdin>' line 1:
"SELECT DATEDIFF(DAY, CURRENT_DATE, TIMESTAMP '2022-09-22 20:30:40') FROM (VALUES(0))"
user lacks privilege or object not found: DAY

sql> SELECT DATEDIFF(day, CURRENT_DATE, TIMESTAMP '2022-09-22 20:30:40') FROM (VALUES(0));
SEVERE  SQL Error at '<stdin>' line 2:
"SELECT DATEDIFF(day, CURRENT_DATE, TIMESTAMP '2022-09-22 20:30:40') FROM (VALUES(0))"
user lacks privilege or object not found: DAY

sql> SELECT DATEDIFF('day', CURRENT_DATE, TIMESTAMP '2022-09-22 20:30:40') FROM (VALUES(0));
16

The current Guide on the web site is for version 2.7.0, which supports both forms of DAY and other units. — fredt, Sep 07 '22 at 19:33
@fredt so now I need to go off and figure out what version I have... So my sqltool says `JDBC Connection established to a HSQL Database Engine v. 2.3.0 database as "SA" with R/W TRANSACTION_READ_COMMITTED Isolation.` And that's why I thought the link I had would be ok. — Jerry Jeremiah, Sep 07 '22 at 21:12

score 1 · Accepted Answer · answered Sep 06 '22 at 21:17

If you're using embedded HSQLDB in LO Base then it's version 1.8. From http://www.hsqldb.org/doc/1.8/guide/guide.html:

DATEDIFF(string, datetime1, datetime2)

returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

So the argument can be either string 'dd' or 'day':

SELECT DATEDIFF ('day', CURRENT_DATE, "Expiry") AS "Days Remaining" FROM "Expirations"

Difference between two dates in HSQLDB

2 Answers2