Subtracting two times in libre office base

Question

How can I subtract two times in a query in libre office base? For instance, 10:30 - 9:00 should give 1:30. Thanks.

EDIT: As it says in the question, I am having this problem in "base", not with the libre office spreadsheet.

Answer 1

SELECT DATEDIFF('hour', "t1", "t2") || ':' || MOD(DATEDIFF('minute', "t1", "t2"),60)
FROM "Table1"

Result:

1:30

It's easier to show the difference as 90 minutes.

SELECT DATEDIFF('minute', "t1", "t2")
FROM "Table1"

Documentation: HSQLDB 1.8 Built-in Functions and Stored Procedures

EDIT:

To determine the engine, try Edit -> Database -> Properties. However, the option is disabled for my example that uses an embedded setup.

So, I unzipped the .odb file and opened database/properties.

#HSQL Database Engine 1.8.0.10

A split database setup does not contain the database directory.

The file content.xml contains database connection information.

<db:connection-data>
    <db:connection-resource xlink:href="sdbc:embedded:hsqldb" xlink:type="simple"/>
    <db:login db:is-password-required="false"/>
</db:connection-data>