[adsense id=”0514458240″ width=”468″ height=”60″]
Using the MySQL function timediff to get the time difference between two dates returns the number of hours, minutes and seconds between the two date times. In most cases this is sufficient, especially if the times are on the same date or within a day of each other. However, in some cases it may return a large number of hours from which we want to identify the number of days.
The MySQL time_format function does not provide a variable to return the number of days so this is something the needs to be calculated.
For example, let us imagine returning the number of hours since the 1st of April 2012 from today:
mysql> SELECT TIMEDIFF(now(), '2012-04-01 09:37:45'); +----------------------------------------+ | TIMEDIFF(now(), '2012-04-01 09:37:45') | +----------------------------------------+ | 512:58:51 | +----------------------------------------+ 1 row in set (0.00 sec)
While this is good the number 512 is large and difficult to determine the number of days. MySQL offers a few nice functions to help us out here:
- TIME_TO_SEC
- This function converts a time data type to an integer representing the number of seconds
- SEC_TO_TIME
- Converts the number of seconds back to a time data type
- FLOOR
- Returns a rounded down integer
- x MOD y
- Returns the remainder when x is divided by y
Calculation
Knowing that the number of seconds will allow us to calculate the number of days, we can use the above functions to help us:
The number of days is the number of seconds divided by 24*60*60 rounded down. This is achieved by issuing FLOOR(seconds / (24 * 60 * 60)).
The number of minutes remaining is the remainder of the above operation. In other words seconds MOD (24 * 60 * 60).
mysql> SELECT Floor(Time_to_sec(Timediff(NOW(), '2012-04-01 09:37:45')) / (
-> 60 * 60 * 24 )) AS
-> days,
-> Sec_to_time(Time_to_sec(Timediff(NOW(), '2012-04-01 09:37:45')) MOD (
-> 60 * 60 * 24 ))
-> AS timediff,
-> Timediff(NOW(), '2012-04-01 09:37:45');
+------+----------+----------------------------------------+
| days | timediff | Timediff(NOW(), '2012-04-01 09:37:45') |
+------+----------+----------------------------------------+
| 21 | 09:07:51 | 513:07:51 |
+------+----------+----------------------------------------+
1 row in set