Base - Calculating Times

[trucksavage]

Is there a way in Base I can calculate the elapsed time between two fields stored as TIME; a StartTime and a FinishTime. If I try to run [FinishTime] - [StartTime] in my query, it appears to return a number that equals the elapsed seconds but I can’t seem to then format the result as HH:MM. Furthermore, if I try to divide the result by 1360, it seems to multiply instead of divide.

LibreOffice 6.1.3.2 Base on Windows 10 (x64)

What am I doing wrong?image description(/upfiles/15422730283604126.jpg)

Attached screen grabs show the table structure and query results.

[Ratslinger]

@trucksavage Please do not post as wiki as it helps no one.

Your linked image is invalid.

This is not a Base question but rather a database question (Base is only the front end to the database). However, you fail to mention what database you are using. Also, if calculating time, what about crossing from one day to the next? Or multiple days? What about timestamps?

[EasyTrieve]

Please first re-write this q not as a wiki, but as yourself, i.e. don’t check the wiki box. Thanks. Also make sure to say what data base you are using, e.g. hsql embedded, or what?

[Ratslinger]

Hello @trucksavage,

Best as can be figured, this is a Firebird embedded database. In the Firebird documentation:

How to calculate hours, minutes or
seconds between two time values?

If you have time values, just subtract
them, and you’ll get the interval
length in seconds:

select end_time - start_time from …

If you want to get minutes, divide the
result by 60, and if you want hours by
3600.

If you have timestamps instead of
times, the difference is in days, so
you if you want seconds, you need to
multiply the value by 60 * 60 * 24.

So one item is why you are trying to divide by 1360?

The result you see of the division is not as you state “…it seems to multiply instead of divide.” The result is actually the correct figure for the division except the decimal point is missing. There are a number of outstanding bugs regarding Firebird concerning numeric and decimal data. This is surely related to that.

Edit:

You should be able to get around this somewhat by casting the result:

CAST((("FinishTime" - "StartTime") / 3600) AS DECIMAL(10,2)) AS "TimeDiff"

which results in hours & hundreths of hours.

[trucksavage]

Thanks for your answer. Yes it is a Firebird Embedded and 1360 was a typo; I meant 3600.
The missing decimal point gave me the impression of multiplication as the number got exponentially bigger - thank you for clarifying that there are known bugs concerning this

[trucksavage]

Thanks for your answer. Yes it is a Firebird Embedded and 1360 was a typo; I meant 3600.
The missing decimal point gave me the impression of multiplication as the number got exponentially bigger - thank you for clarifying that there are known bugs concerning this