Bug #9905 AVG() on a datetime/timestamp does not return expected values
Submitted: 14 Apr 2005 14:06 Modified: 14 Apr 2005 14:46
Reporter: [ name withheld ] Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:4.1.11 rpm OS:Linux (RHEL 3 update 4)
Assigned to: CPU Architecture:Any

[14 Apr 2005 14:06] [ 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)))

This is the reopening of bug #9874 as a feature request as per Sergei Golubchik's instructions.

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?