Comparing Dates in Openoffice Database with HSQLDB

Question

I have a table in an Openoffice Database, containing two date columns. I'm trying to create a view that will aggregate all the rows in these columns and sum up the difference between these two columns in minutes.

This is pretty trivial, and in Postgres it would look like:

SELECT SUM(EndDate-BeginDate) AS total_minutes FROM mytable

What is the equivalent syntax in HSQLDB? I can't find much documentation on their date functions, and all the "standard" methods I'm familiar with return a syntax error.

Answer 1

It's a similar syntax in HSQLDB 2.0

SELECT SUM((EndDate-BeginDate) MINUTE) AS total_minutes FROM mytable

In HSQLDB 1.8.x used in OpenOffice, try the DATEDIFF() function as documented in the HSQLDB Guide