Bug #15595 Aggregate functions truncate time values to whole hours.
Submitted: 8 Dec 2005 16:44 Modified: 18 Jan 2006 17:11
Reporter: Lisbeth Kellogg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.11 OS:Solaris (Solaris)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[8 Dec 2005 16:44] Lisbeth Kellogg
Description:
Time values are truncated (not rounded) to whole hours before being used in aggregate functions like sum() and avg().  This results in serious inaccuracies in the aggregated values.

How to repeat:
Execute:

select '01:45:00',avg('01:45:00') from mysql.db limit 1

Row values returned are:

'01:45:00', 1

Should be:

'01:45:00', 1.75
[8 Dec 2005 17:11] Valeriy Kravchuk
Thank you for a problem report. In fact, the right definition of "average time" is not so obvious, but yes, the statement you proposed works just as you described, even on latesty 4.1.17-BK:

mysql> select '01:45:00',avg('01:45:00') from mysql.db limit 1;
+----------+-----------------+
| 01:45:00 | avg('01:45:00') |
+----------+-----------------+
| 01:45:00 |               1 |
+----------+-----------------+
1 row in set (0.04 sec)

mysql> select '02:01:00',avg('02:01:00') from mysql.db limit 1;
+----------+-----------------+
| 02:01:00 | avg('02:01:00') |
+----------+-----------------+
| 02:01:00 |               2 |
+----------+-----------------+
1 row in set (0.00 sec)

mysql> select '01:45:00' + 1,avg('01:45:00') from mysql.db limit 1;
+----------------+-----------------+
| '01:45:00' + 1 | avg('01:45:00') |
+----------------+-----------------+
|              2 |               1 |
+----------------+-----------------+
1 row in set (0.04 sec)

Even if you explicitely convert that string to time:

mysql> select TIME('01:45:00') ,avg(TIME('01:45:00')) from mysql.db limit 1;
+------------------+-----------------------+
| TIME('01:45:00') | avg(TIME('01:45:00')) |
+------------------+-----------------------+
| 01:45:00         |                     1 |
+------------------+-----------------------+
1 row in set (0.00 sec)

I think, AVG() simply convert the value to number (up to the first non-numeric part). Here is the woraround that can be used:

mysql> select TIME_TO_SEC('01:45:00') ,avg(TIME_TO_SEC('01:45:00')) from mysql.db limit 1;
+-------------------------+------------------------------+
| TIME_TO_SEC('01:45:00') | avg(TIME_TO_SEC('01:45:00')) |
+-------------------------+------------------------------+
|                    6300 |                    6300.0000 |
+-------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.17    |
+-----------+
1 row in set (0.00 sec)

See comments at  http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html. But I agree, that this behaviour of AVG() aggregate function should be documented explicitely at that page.
[8 Dec 2005 18:19] Lisbeth Kellogg
Sum() shows the same behavior.
[8 Dec 2005 18:26] Lisbeth Kellogg
TIME_TO_SEC() seems to be the best solution.  Thank you.

By the way in the query I'm working on I have a max() aggregate.  It is leaving the data type of the result as time, but appears to be dropping off one of the seconds digits for all rows in the result set.  E.g.: '02:22:4'.  I have not found a simple query to demonstrate this however.
[18 Jan 2006 17:11] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).