A recent project had us dealing with the differences in whole days between two dates stored in a MySQL table. As a first effort, you may turn to the trusty DATEFIFF() function to return the interval. This works well when the dates you have to diff are in the same timezone as the dates you want to display but breaks down when, for instance, your dates are stored in UTC (as they should be) and are displayed in a local timezone.
Take this case of a datetime stored as UTC in a table – ’2009-10-24 23:00:00′ compared against the current UTC_TIMESTAMP() of, say, ’2009-10-23 02:00:00′.
A simple DATEFIFF() will return 1 as the day difference between the two datetimes -which is correct.
Now let’s take a look at those dates when displayed, for instance as UTC + 10. The first datetime becomes ’2009-10-25 09:00:00′ and the second becomes ’2009-10-23 12:00:00′. If we’re displaying those local datetimes as dates on a website along with the day difference, we’ll get 23/10/2009 – 25/10/2009 (1 day) – as an example, which is plainly wrong. We could do the diff in scriptland but then MySQL comes with excellent date mathematics functions already.
The issue is that DATEDIFF ignores the time part of the timestamp, only working on the date part.
MySQL provides another method called TIMEDIFF, returning a time value of the datetime difference. In this example, the time value returned is 45:00:00 (i.e 45 hours). This is great, as you can now write a query something like
SELECT ROUND(EXTRACT(HOUR FROM TIMEDIFF('2009-10-24 23:00:00', '2009-10-23 02:00:00')) / 24)
Which is a bit of a mess, but gives the required result of 2 days. Solved? yes, until you hit something like the following:
SELECT ROUND(EXTRACT(HOUR FROM TIMEDIFF('2009-10-23 02:00:00', '2008-05-29 23:00:00'))/ 24)
… and suddenly the day difference becomes 35! The problem here is that “TIME values may range from '-838:59:59' to '838:59:59'" (quoting the MySQL TIME documentation), which happens to be about 35 days.
You may tear your hair out here unless you look at the TIMESTAMPDIFF function, which will do exactly what you want, regardless of timezone:
SELECT ROUND(TIMESTAMPDIFF(HOUR, '2009-10-23 02:00:00', '2009-10-24 23:00:00') / 24)
Noting that the query uses HOUR as the interval in order to get a fractional day that can be rounded to the nearest integer day after division.
