Bug #9874 AVG() on a datetime/timestamp does not return expected values
Submitted: 13 Apr 2005 15:43 Modified: 14 Apr 2005 11:35
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 rpm OS:Linux (RHEL 3 update 4)
Assigned to: CPU Architecture:Any

[13 Apr 2005 15:43] [ name withheld ] 
Description:
AVG() on a datetime/timestamp columns goes through a rounding process and returns the incorrect results.  A workaround is to do the following FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(col)))

How to repeat:
AVG(col) v FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(col)))
20050258324839 v 2005-03-24 02:05:11

Suggested fix:
Make AVG on a datetime/timestamp work correctly as it does in other databases like SQL server.  Until a fix is put in the place the documentation could be updated to indicate the current behavior.  Also, shouldn't this implicit type conversion at least result in a warning?
[13 Apr 2005 21:40] Jorge del Conde 
Tested w/4.1 from bk
[14 Apr 2005 11:35] Sergei Golubchik 
This is not a bug. AVG() expects a numeric argument.
And when a date, e.g. "2005-04-04 13:31:16" is converted to a number you get
20050404133116. Computing the average for these numbers makes little sense :)
Using UNIX_TIMESTAMP/FROM_UNIXTIME is the correct solution,

To let MySQL average dates correctly is a valid suggestion, but implementing it is not a bugfix.
Feel free to reopen the report, changing the severity to S4 - "feature request".