Average Time Query

[omgm1ke]

I have come stuck with averaging a column with type of Time.

I know there is a function AVG() that I can use to filter the average of a column, however, this brings back an integer.

Is there any way to return the average in time format (IE 00:00:00)?

BACKGROUND INFO:

SELECT `estimatedTime`
FROM job_sheet;

Returns,

01:00:00
21:30:00
01:00:00

The data type is time, and the default is 00:00:00.

 

[rac2]

Have you tried?

SELECT CAST( AVG(estimatedTime) AS TIME )
FROM job_sheet

 

[r937]

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(estimatedTime)))
as avg_time
FROM job_sheet


r937.com | rudy.ca

 

[omgm1ke]

Thanks r937, your query worked a charm!

rac2, your query brought back NULL, but thanks anyway.

 

[rac2]

Thanks for the report. Rudy is good, no doubt.