Computing the difference between two time values by means of SQL

Two columns of a table (for the database engine “HyperSQL” within the application “LibreOffice Base 7.1.0.3-985.1”) have got the data type “TIME”.
I would like to calculate a difference for these fields.

It seems that the following SQL statement is insufficient so far.

SELECT ID, t1, t2, t2 - t1 AS duration FROM time_measurements_test

I would appreciate your advices.

Would you like to point further information sources out besides query examples which work with dates and time stamps?

I have no idea what the format is of the type TIME.

In Unix (and thus Linux) time stamps are in milliseconds since the Epoch (1970-01-01 00:00:00.000, which is thus time stamp 0).
Easy to calcultate the difference in milliseconds between two.

But when your’s is some character format, it is much less easier.

@elfring:

Maybe, take a look at the HyperSQL “Interval Types” – <https://hsqldb.org/doc/guide/sqlgeneral-chapt.html#sgc_interval_typs> …

The format of a TIME column is (was when I last checked) HH:MM:SS with microsecond precision; -838:59:59.999999 to 838:59:59.999999; with the delimiters, they are treated as time of day; without the delimiters as elapsed time.

‘duration’ is a temporary column and must not appear in any table in use; ‘time_measurements_test’ is presumably the table which holds t1 and t2; I assume you have terminated the expression with ;

It refers to a computed field.

and must not appear in any table in use; ‘time_measurements_test’ is presumably the table which holds t1 and t2; …

This is the case for another clarification attempt according to database applications also together with the software “LibreOffice Base 7.1.0.3-985.1”.

  • Are there any additional data type conversion functions needed for the desired subtraction of a value from a time field by the means of the structured query language?
  • How do you think about to work with floating-point numbers for such an use case?

May be this

https://www.hsqldb.org/doc/guide/builtinfunctions-chapt.html#bfc_datetime_arithmetic

helps.

Regards

susejunky

How much do the referenced (datetime) functions matter when the difference between TIME values (and not date/time stamps) should be determined?

Did you work through the rest of that manual?

If i recall correctly it contains a chapter on data types and explains how one can convert between different data types.

Did you try to convert your TIME values to TIMESTAMP values and then use TIMESTAMPDIFF ?

Regards

susejunky

Beware that manual is for HyperSQL database engine 2.6.0 whereas LibreOffice 7.x still uses HyperSQL database engine 1.8.0.10.

However according to https://wiki.documentfoundation.org/images/7/71/Base_Gesamtband_einseitig_V70.pdf (i could not find an English Version of that document) the function DATEDIFF should do the job even without converting the TIME values.

Regards

susejunky

I got distracted by the key word “DATE” while I was looking for the support of an other time unit.
An SQL statement like the following can fit to my application needs.

SELECT "day", "start", "stop",
       DATEDIFF( 'minute', "start", "stop" ) / 60.000 AS "duration"
FROM "time_measurements";

Will this technical detail trigger any further software development concerns?

… the function DATEDIFF should do the job even without converting the TIME values.

Will any adjustments become more helpful also for the software documentation?

Sorry but i have problems to understand what you are asking here (I’m no native English speaker!).

If your first question is how the current HyperSQL-Version of LibreOffice Base could impact your software development then all i can say is: The German LibreOffice Base manual states that there are plans to replace HSQLDB with Firebird “in the future” and the manual includes a table which shows the functional differences between those two engines.

Depending on your needs using an external database engine like mariadb or PostgreSQL might offer more flexibility and a better upgrade path.

However it would be best to consult the LibreOffice forum or mailing list on that topic.

Could you please elaborate on what you understand by “adjustments” and “become more helpful” in your second question?

Regards

susejunky

We might be looking for ways to influence the software evolution in desirable directions (besides our discussion).

LibreOffice is a community driven project. So if you want to help them to improve their software and/or documentation i think you will be welcome.

Regards

susejunky

With the current version 6.4.5.2 of LibreOffice shipping with Leap 15.2, the release notes state the following <LibreOffice 6.4: Release Notes - The Document Foundation Wiki; –

Firebird embedded database creation put back experimental considering the number of bugs.

  • Despite, the “firebird” and “libreoffice-base-drivers-firebird” packages being installed by default …
  • And, “Firebird” being mentioned in the system-wide LibreOffice configuration files …

It gets worse, the English handbooks are not available for LibreOffice 7.0 – the LibreOffice 6.4 Base handbook doesn’t mention the Firebird issues – the LibreOffice 7.0 German handbook does …

  • If and when differences between “HSQLDB” and “Firebird” exist then, the handbook text contain marked-up text related to the differences …