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