I’m very new to Libre base and trying to make a small database.
I have a Time field (HH:MM:SS) named Length that I need to do some calculations with. (In a query I want to find the average of all the times)
I know time is not a numeric and can’t be added or subtracted. I been trying to find a tutorial online how to convert the HH:MM:SS to a decimal and than the decimal back to time but have failed. Is there a way to do what I’m thinking has to be done or a reliable reference how to do it?
I thought of this solution into 3 phases, 3 queries:
Query 1: turns everything in seconds
SELECT HOUR( "Length" ) * 3600 + MINUTE( "Length" ) * 60 + SECOND( "Length" ) "Seconds" FROM "Table1"
Query 2: averages the values entered in seconds (the FLOOR function truncates the result to a smaller integer)
SELECT FLOOR( AVG( "Seconds" ) ) "Media" FROM "Query1"
Query3: calculates and convert the format result from seconds to HH:MM:SS (function CAST (…) AS INTEGER, formats the number to integer)
SELECT CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) || ':' || CAST( FLOOR( "Media" / 60 ) AS INTEGER ) - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 60 || ':' || CAST( ( "Media" - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 3600 - ( CAST( FLOOR( "Media" / 60 ) AS INTEGER ) - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 60 ) * 60 ) AS INTEGER ) "Media Time" FROM "Query2"
A little complicated, but it works !
If my answer helped you, vote it with 
and with ∧ (top on the left)