Ask Your Question

# Libre base: finding average of time

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?

edit retag close merge delete

## 1 Answer

Sort by » oldest newest most voted

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)

more

## Stats

Asked: 2016-01-11 00:46:58 +0100

Seen: 919 times

Last updated: Jan 12 '16