[adsense id=”0514458240″ width=”468″ height=”60″]
This post provides instructions on how to calculate to the difference between two times rounding up to the nearest minute.
Let’s imagine you are trying to process a system which tracks the checkin time and the checkout time in a day. We want to charge someone per minute to the nearest minute.
In this example we have created a time called “timer”:
mysql> create table timer(
-> starttime time,
-> endtime time
-> );
Query OK, 0 rows affected (0.03 sec)
We then populate this with specific information as follows:
mysql> insert into timer (starttime, endtime) values (time_format('09:00:00', '%H:%i:%s'), time_format('09:01:00', '%H:%i:%s'));
Query OK, 1 row affected (0.00 sec)
Calculation
MySQL provides some useful functions called TIME_TO_SEC which converts a time to seconds from 00:00:00. There is also an inverse function called SEC_TO_TIME which converts the seconds back to a time. We will use both in calculating the difference.
In the most basic cases, the difference between the two is as follows:
mysql> select endtime, starttime, sec_to_time(time_to_sec(endtime) - time_to_sec(starttime)) timediff, time_to_sec(endtime) - time_to_sec(starttime) secdiff from timer; +----------+-----------+----------+---------+ | endtime | starttime | timediff | secdiff | +----------+-----------+----------+---------+ | 17:05:23 | 09:09:53 | 07:55:30 | 28530 | | 09:09:01 | 09:09:00 | 00:00:01 | 1 | | 09:09:30 | 09:09:00 | 00:00:30 | 30 | | 09:09:59 | 09:09:00 | 00:00:59 | 59 | | 09:00:30 | 09:00:00 | 00:00:30 | 30 | | 09:01:59 | 09:00:00 | 00:01:59 | 119 | | 09:01:00 | 09:00:00 | 00:01:00 | 60 | | 09:01:01 | 09:00:00 | 00:01:01 | 61 | +----------+-----------+----------+---------+ 8 rows in set (0.00 sec)
The timediff field returns in time format the difference and the secdiff gives the difference in seconds.
Rounding
In the case of rounding up whether it is to the nearest minute or the nearest hour this can be achieved by doing the following calculation:
CEIL(difference / 60) * 60
The CEIL effectively rounds up the result of difference divided by 60 resulting in an INTEGER being returned. If this INTEGER is then multiplied by 60 we get an exact number of minutes. Here is an example:
Example Difference: 121 seconds 121 / 60 = 2.01666 CEIL(121 / 60) = 3 3 * 60 = 180 180 = 00:03:00
Let’s imagine we need to do the same thing but this time round to the nearest hour:
Example Difference: 3645 seconds = 1 hour and 45 seconds ( we expect this to return 2 hours) 3646 / (60mins * 60secs) = 1.0125 CEIL(3646 / (60mins * 60secs)) = 2 2 * (60mins * 60secs) = 7200 7200 seconds = 02:00:00
In our data above we would write the query in MySQL as follows:
mysql> SELECT Sec_to_time(Time_to_sec(endtime) - Time_to_sec(starttime)) timediff,
-> Time_to_sec(endtime) - Time_to_sec(starttime) secdiff,
-> Sec_to_time(Ceil(( Time_to_sec(endtime) - Time_to_sec(starttime) ) / 60)
-> * 60)
-> roundeddiff,
-> starttime,
-> endtime
-> FROM timer;
+----------+---------+-------------+-----------+----------+
| timediff | secdiff | roundeddiff | starttime | endtime |
+----------+---------+-------------+-----------+----------+
| 07:55:30 | 28530 | 07:56:00 | 09:09:53 | 17:05:23 |
| 00:00:01 | 1 | 00:01:00 | 09:09:00 | 09:09:01 |
| 00:00:30 | 30 | 00:01:00 | 09:09:00 | 09:09:30 |
| 00:00:59 | 59 | 00:01:00 | 09:09:00 | 09:09:59 |
| 00:00:30 | 30 | 00:01:00 | 09:00:00 | 09:00:30 |
| 00:01:59 | 119 | 00:02:00 | 09:00:00 | 09:01:59 |
| 00:01:00 | 60 | 00:01:00 | 09:00:00 | 09:01:00 |
| 00:01:01 | 61 | 00:02:00 | 09:00:00 | 09:01:01 |
+----------+---------+-------------+-----------+----------+
8 rows in set (0.00 sec)
The roundeddiff field contains the time rounded upwards to the nearest minute.
If you want to round down the results to the nearest minute replace the function CEIL with FLOOR:
mysql> SELECT Sec_to_time(Time_to_sec(endtime) - Time_to_sec(starttime)) timediff,
-> Time_to_sec(endtime) - Time_to_sec(starttime) secdiff,
-> Sec_to_time(Floor(( Time_to_sec(endtime) - Time_to_sec(starttime) ) / 60)
-> * 60)
-> roundeddiff,
-> starttime,
-> endtime
-> FROM timer;
+----------+---------+-------------+-----------+----------+
| timediff | secdiff | roundeddiff | starttime | endtime |
+----------+---------+-------------+-----------+----------+
| 07:55:30 | 28530 | 07:55:00 | 09:09:53 | 17:05:23 |
| 00:00:01 | 1 | 00:00:00 | 09:09:00 | 09:09:01 |
| 00:00:30 | 30 | 00:00:00 | 09:09:00 | 09:09:30 |
| 00:00:59 | 59 | 00:00:00 | 09:09:00 | 09:09:59 |
| 00:00:30 | 30 | 00:00:00 | 09:00:00 | 09:00:30 |
| 00:01:59 | 119 | 00:01:00 | 09:00:00 | 09:01:59 |
| 00:01:00 | 60 | 00:01:00 | 09:00:00 | 09:01:00 |
| 00:01:01 | 61 | 00:01:00 | 09:00:00 | 09:01:01 |
+----------+---------+-------------+-----------+----------+
8 rows in set (0.00 sec)