MySQL Bugs: #12108: sum() for datatype time

Bug #12108	sum() for datatype time
Submitted:	22 Jul 2005 12:40	Modified:	25 Jul 2008 7:58
Reporter:	Andreas Mller	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	4.0.18, 4.1.12. 5.0.9	OS:	Linux (Linux, Windows)
Assigned to:		CPU Architecture:	Any

Description:
Selecting the sum of a column of time values results in a very strange result and not in the expected sum.

SELECT Sum(col) FROM table

In my case the sum of 19 rows should be 03:02:35. The result of the query was 17915. 10955 seconds would be correct is this should be seconds.

When I make 

SELECT Sec_to_Time(Sum(Time_to_Sec(col))) FROM table

the result is correct.

Because SQL is a 4GL language I except the function will calculate the sum of the values - or - will fail with an error message. So the result of the sum is realy a bug and should be fixed.

How to repeat:
SELECT Sum(col) FROM table

Please, try your test case on 4.0.25 (or, better, on 4.1.13 - the recommended branch). You can download 4.0.25 from http://dev.mysql.com/downloads/mysql/4.0.html

Section on TIME data type in the manual may be useful for you too: http://dev.mysql.com/doc/mysql/en/time.html

More detailed test case will be also useful. Like the following:

mysql> create table t3 (c time);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t3 values(current_time);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t3 values(current_time);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t3 values(current_time);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
+----------+
| c        |
+----------+
| 15:56:09 |
| 15:56:10 |
| 15:56:11 |
+----------+
3 rows in set (0.02 sec)

mysql> select sum(c) from t3;
+--------+
| sum(c) |
+--------+
| 466830 |
+--------+
1 row in set (0.00 sec)

select 155609+155610+155611;
+----------------------+
| 155609+155610+155611 |
+----------------------+
|               466830 |
+----------------------+
1 row in set (0.02 sec)

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

So, my test clearly shows that there is no such a problem on 4.1.12 on Windows.

Ok let's make an example:
I've 3 music files with the following length:
00:52:12
01:45:07
00:48:34
The total length of this 3 files is 03:25:53. But the sum reports 24553.

mysql> create table music(length time);
Query OK, 0 rows affected (0.22 sec)

mysql> insert into music values('00:52:12');
Query OK, 1 row affected (0.02 sec)

mysql> insert into music values('01:45:07');
Query OK, 1 row affected (0.00 sec)

mysql> insert into music values('00:48:34');
Query OK, 1 row affected (0.00 sec)

mysql> select sum(length) from music;
+-------------+
| sum(length) |
+-------------+
|       24553 |
+-------------+
1 row in set (0.00 sec)

This was now tested on 4.0.24. You testcase looks like the same problem.

There are some mistakes clear:
- minutes and seconds are on base 100 not on base 60
- the datatyp is silently changed

I would excpet the following result from the select:

mysql> select sum(length) from music;
+-------------+
| sum(length) |
+-------------+
|    03:25:53 |
+-------------+
1 row in set (0.00 sec)

We are adding time(!) values not numbers, digits or something else. So if I add time values the result is also a time value and should be calculated like time values are handled. MySQL seems to add the three parts separatly on an numberbase of 100 but this is wrong. You can see this in my excample: The sum of the minute part is 145 so MySQL takes the 45 and adds 1 to the hour part.

Sorry for misinterpretation of your test case. You are right in your conclusions about how TIME values are "added" - they are simply implicitely converted to numbers. And then added as numbers. The resulting NUMBER is selected.

I could not find any place in documentation that explicitely describe how "adding" (with + operator, or SUM aggregation function) should work for TIME values.

The same applies to strings in some cases:

mysql> select sum('abc') from music;
+------------+
| sum('abc') |
+------------+
|          0 |
+------------+
1 row in set, 3 warnings (0.00 sec)

Being unable to add strings, MySQL tried to convert them to numbers, and, as described in:

http://dev.mysql.com/doc/mysql/en/design-limitations.html

was forced to use "appropriate" value - 0 in this case, INSTEAD of error message, as you may expected. So, it is not a bug of MySQL engine - just an unclear consequence of (documented) design decision.

You also found a good workaround. Your way of calculating sum of times has been already described in user comments:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
http://dev.mysql.com/doc/mysql/en/group-by-functions.html

I think, all is needed is clear description of this behaviour in documentation, on the pages devoted to SUM fucntion and TIME data type. Hope this will be done finally.

Thank you for your bug report.

You explanation of adding strings is right and I can agree with this behavior.
But for time values I think it should be "naturaly" handled. If you adding time values in witch way would you add them?
It is clear when I try to add string values that something "strange" will be the result. But there is the explicit datatype "time". So it should be absolutly clear in witch way time values are naturaly added.
I also found no place in the documentation that describe the behavior of adding time values. But my conclusion is that it should be added naturaly - because you won't find a place where explicitly the adding of integer is described.
The workaround with the functions time_to_sec and sec_to_time (look at my first examples) works find for me. Thats why the bug is only S3.
Finaly this "strange" behavior is still a bug for me. What can we do to get a solution? Changing the documentation will not be the right solution I think :-)

For now we have the following:

SUM() works exactly as + does. For all data types.

I discussed this issue with developers. A (correct) solution should be provided in one of the future versions, but I can't give you version number. So it is "To be fixed later".

I am using the Community Server Version 5.0.27 on WinXP Home. Todays date is February 18, 2007. Do you have any information on which future version(s) this issue will be fixed? Also, what would you suggest as a workaround until it is addressed.

Sorry. Forgot to mention that I observed this condition on the build I am using (V5.0.27).

I am using the Mysql version: 5.0.27-community-nt

The problem still exists after a year.
Can you please update this thread if this problem had been resolved or not.

Is there any plan to rectify this behavior.
I totally agree with that since the datatype is defined as TIME and there is a specified procedure to SUM the time, it should be added to the feature set.

Pradeep Agnihotri.

Hi guys, I had the same issue and thought it was me until i started looking.

here's a workaround:

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `t_hours` ) ) ) AS total_time FROM `time`;

Workarounds are not the problem. The problem is that I excpet a natural behavior of datatypes but for MySQL this is a "Feature Request". If they quarrel with backward compatibility so they have a sql strict mode in with they could solve the problem. But we'r waiting 2 years now and nothing is done.

Why after sum reaches a certain number it returns null?

mysql> select * from tracking where source='Numara';
+--------+------------+----------+----------+----------+----------+----------+
| source | date       | timein   | timeout  | ttime    | lunchin  | lunchout |
+--------+------------+----------+----------+----------+----------+----------+
| Numara | 2008-09-08 | 09:00:00 | 19:00:00 | 10:00:00 | 00:00:00 | 00:00:00 |
| Numara | 2008-09-09 | 14:20:00 | 19:00:00 | 04:40:00 | 00:00:00 | 00:00:00 |
| Numara | 2008-09-10 | 09:32:00 | 19:07:43 | 08:59:56 | 15:07:17 | 14:31:30 |
+--------+------------+----------+----------+----------+----------+----------+
3 rows in set (0.02 sec)

mysql> select sum(ttime) from tracking where source='Numara';
+------------+
| sum(ttime) |
+------------+
|     229956 |
+------------+
1 row in set (0.00 sec)

mysql> select subtime(timediff(timeout,timein), timediff(lunchin,lunchout)) from
 tracking where source='Numara';
+---------------------------------------------------------------+
| subtime(timediff(timeout,timein), timediff(lunchin,lunchout)) |
+---------------------------------------------------------------+
| 10:00:00                                                      |
| 04:40:00                                                      |
| 08:59:56                                                      |
+---------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select time(sum(subtime(timediff(timeout,timein), timediff(lunchin,luncho
ut)))) from tracking where source='Numara' and date <> date(now());
+--------------------------------------------------------------------------+
| time(sum(subtime(timediff(timeout,timein), timediff(lunchin,lunchout)))) |
+--------------------------------------------------------------------------+
| 14:40:00                                                                 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select time(sum(subtime(timediff(timeout,timein), timediff(lunchin,luncho
ut)))) from tracking where source='Numara';
+--------------------------------------------------------------------------+
| time(sum(subtime(timediff(timeout,timein), timediff(lunchin,lunchout)))) |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Quote: [27 Jul 2005 7:14] Valeriy Kravchuk
For now we have the following:

SUM() works exactly as + does. For all data types.

I discussed this issue with developers. A (correct) solution should be provided in one of the future versions, but I can't give you version number. So it is "To be fixed later".

---

The year is 2012, 7 years later and a lot of versions have been released.
Any word on when or if this will be fixed?