Formating a time field using Avg

[cs3mw]

Hi I am trying to get an average reading on a time field so say for instance I have two values

00:00:02 and
00:11:00

I get the value 551 which is basically 1102 / 2 which is incorrect because the value I really want is 00:05:31. Has anyone got an idea how I can do this. Thanks in advance!

[chazzy]

what dbms? what are the fields defined as in the db?

[cs3mw]

Its a mysql database and the name of the field is completetime. Apoligies

[chazzy]

i don't care about the name of the field, what type is it defined as?

Assuming that it's defined as a TIME type, you probably want to use time_to_sec (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_time-to-sec) to convert the time type to a # of seconds and apply the average on that, then sec_to_time (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_sec-to-time) to convert the resulting seconds into a time format.

[cs3mw]

Thanks for that! I did put that the field was a time field on my first line

Hi I am trying to get an average reading on a time field so say for instance I have two values

[NogDog]

Thanks for that! I did put that the field was a time field on my first line
But you'd be amazed how many times people have a field called "date" or "time" that is a char/varchar or integer field. :rolleyes: