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.

Answer 1

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"

Answer 2

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