Ask Your Question
0

Libre base: finding average of time

asked 2016-01-11 00:46:58 +0200

MMistretta gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-01-12 21:33:37 +0200

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)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 811 times

Last updated: Jan 12 '16