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)?

 

[AlexCuse]

I would convert to seconds, then take the average of the seconds, and then convert back.

What does your data look like now?

Alex

Ignorance of certain subjects is a great part of wisdom

 

[omgm1ke]

Ok, for example:

SELECT AVG( `estimatedTime` )
FROM job_sheet;

Returns:

77666.666666667

Which should be something like 07:76:66 (ignoring the everything after the decimal). Which obviously should be 08:17:06... I think... you get what I mean LOL. It should be this because it is the answer based on the 3 entries in the column.

 

[AlexCuse]

Can you show what the actual data is. Like what does

select estimatedTime
from job_sheet

give? Just a few rows will do. Then we can figure out best way to average it.

Ignorance of certain subjects is a great part of wisdom

 

[omgm1ke]

Cool,

SELECT `estimatedTime`
FROM job_sheet;

Returns,

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

If the data in the table were all 01:00:00 the AVG query returns 10000. I threw in the 21:30:00 to test.

 

[AlexCuse]

Two more question, is it ALWAYS going to be in this format?

HH:MM:SS

If you always will have the the two colons present, it will be fairly easy to get this to seconds.

Also, what is the data type of this column (My guess would be char(8))

Ignorance of certain subjects is a great part of wisdom

 

[omgm1ke]

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

Thinking about it, the best default will peobably be 00:00. However, for the sake of getting this problem sorted I will leave it at 00:00:00.

 

[gmmastros]

Something like this...

[tt][blue]
Select DateAdd(Second, Avg(DateDiff(Second, 0, Convert(DateTime, EstimatedTime))), 0)
From Table
[/blue][/tt]

This assumes that there is no Date Component in the field and that the field is a varchar/char (8).



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Well, looking at your query I am thinking that you are probably using mySQL, so I am not certain how helpful any of this will be. Here is the query I came up with.

select
avg(dateDiff(second, 0, estimatedTime))
,dbo.SecTimeDay(avg(dateDiff(second, 0, estimatedTime)))
from @job_sheet

The function dbo.SecTimeDay is used to get average seconds back into HH:MM:SS format, and can be found here: faq183-6519

For mySQL, you should check here:forum436

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[omgm1ke]

Thanks for all the help guys. It's got me on the way.