MySQL Bugs: #15595: Aggregate functions truncate time values to whole hours.

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

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

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.

Sum() shows the same behavior.

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.

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