Author: admin

[adsense id=”0514458240″ width=”468″ height=”60″]

I have seen this requested many times especially for reporting. Let’s imagine we need to generate a report which displays hours and minutes and a sum of the reported times. Imagine we have the following information in our database:

mysql> select starttime, endtime, diff, time_format(diff, '%H:%i') reportedtime from timer;
+-----------+----------+----------+--------------+
| starttime | endtime  | diff     | reportedtime |
+-----------+----------+----------+--------------+
| 09:09:53  | 17:05:23 | 07:55:30 | 07:55        | 
| 09:09:00  | 09:09:01 | 00:00:01 | 00:00        | 
| 09:09:00  | 09:09:30 | 00:00:30 | 00:00        | 
| 09:09:00  | 09:09:59 | 00:00:59 | 00:00        | 
| 09:00:00  | 09:00:30 | 00:00:30 | 00:00        | 
| 09:00:00  | 09:01:59 | 00:01:59 | 00:01        | 
| 09:00:00  | 09:01:00 | 00:01:00 | 00:01        | 
| 09:00:00  | 09:01:01 | 00:01:01 | 00:01        | 
+-----------+----------+----------+--------------+
8 rows in set (0.01 sec)

So far so good. By what if we perform a sum on the field “diff”.

mysql> select sec_to_time(sum(time_to_sec(diff))) from timer;
+-------------------------------------+
| sec_to_time(sum(time_to_sec(diff))) |
+-------------------------------------+
| 08:01:30                            | 
+-------------------------------------+
1 row in set (0.00 sec)

While this is accurate it does not match the reported hours and minutes:

mysql> select time_format(diff, '%H:%i') reportedtime from timer;
+--------------+
| reportedtime |
+--------------+
| 07:55        | 
| 00:00        | 
| 00:00        | 
| 00:00        | 
| 00:00        | 
| 00:01        | 
| 00:01        | 
| 00:01        | 
+--------------+
8 rows in set (0.00 sec)

As a result we need to round the content to the nearest minute before performing the calculation. In order to round there are several ways to perform this:

• Using FLOOR(time / 60) * 60 which rounds the content down returning an integer
• Using the (time DIV 60) * 60 operator which performs a division returning an integer

Here is an example of both operations:

mysql> SELECT Sec_to_time(Sum(Time_to_sec(diff)))                  accurate,
    ->        Sec_to_time(Sum(Time_to_sec(diff) DIV 60) * 60)      roundeddiv,
    ->        Sec_to_time(Sum(Floor(Time_to_sec(diff) / 60)) * 60) roundedfloor
    -> FROM   timer;  
+----------+------------+--------------+
| accurate | roundeddiv | roundedfloor |
+----------+------------+--------------+
| 08:01:30 | 07:58:00   | 07:58:00     | 
+----------+------------+--------------+
1 row in set (0.01 sec)