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.
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 ;
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?
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";
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?
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 …