Thread: Computing the difference between two time values by means of SQL

1. Busy Penguin
Join Date
Dec 2008
Posts
412

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?

2. Re: Computing the difference between two time values by means of SQL

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.

3. Omniscient Penguin
Join Date
Feb 2010
Location
Germany
Posts
3,967

Re: Computing the difference between two time values by means of SQL

@elfring:

Maybe, take a look at the HyperSQL “Interval Types” – <https://hsqldb.org/doc/guide/sqlgene..._interval_typs> …

4. Omniscient Penguin
Join Date
Jun 2008
Location
West Yorkshire, UK
Posts
3,570

Re: Computing the difference between two time values by means of SQL

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 ;

5. Busy Penguin
Join Date
Dec 2008
Posts
412

Re: Computing the difference between two time values by means of SQL

Originally Posted by john_hudson
‘duration’ is a temporary column
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?

6. Re: Computing the difference between two time values by means of SQL

Originally Posted by elfring
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?
May be this

https://www.hsqldb.org/doc/guide/bui...ime_arithmetic

helps.

Regards

susejunky

7. Busy Penguin
Join Date
Dec 2008
Posts
412

Re: Computing the difference between two time values by means of SQL

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

8. Re: Computing the difference between two time values by means of SQL

Originally Posted by elfring
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

9. Re: Computing the difference between two time values by means of SQL

Originally Posted by susejunky
Did you work through the rest of that manual?
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/...seitig_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

10. Busy Penguin
Join Date
Dec 2008
Posts
412

Re: Computing the difference between two time values by means of SQL

Originally Posted by susejunky
Did you try to convert your TIME values to TIMESTAMP values and then use TIMESTAMPDIFF ?
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.

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

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•