Comparing Dates

Warning: Comparing dates can make you rip your hair out. The difference of two dates provides a difficult task because the people who made our time structure obviously did not care to consider the future programmers that have to deal with it every day. Using MySQL, the task is much easier if you know a few MySQL Functions like “DATEDIFF” and “TIMESTAMPDIFF”. The idea is that the MySQL functions do all of the hard work and you just input the particular values into the function. In this tutorial, we will compare 2 dates and output the dates in the difference of days and the difference of seconds.

MySQL DATEDIFF Function

SELECT DATEDIFF(‘2013-01-14 10:59:10′,’2013-01-17 11:50:34’)*24*60*60;
#259200

The DATEDIFF function is not as robust as it appears. We provided the trailing “*24*60*60” to convert the returned value into seconds. In the next example you will see why I have done this and why the TIMESTAMPDIFF is much more accurate. The DATEDIFF can be deceiving when I put in full date times, but I do not want to confuse you. The DATEDIFF could not care less about the time string (ex: “10:59:10”), in fact you should remove them to remind yourself of the difference. MySQL looks at our 2 dates and when it compares them it sees that we have the same year and month. However, the days are different so it subtracts the second date’s day from the first date’s day (17-14), which gives us 3. Now, 3*24*60*60 = 259200 like any good mathematician would tell you. That number is the difference of days in seconds, but not the difference of seconds between the two datetimes. The TIMESTAMPDIFF on the other hand can do a datetime second comparison:

MySQL TIMESTAMPDIFF Function

SELECT TIMESTAMPDIFF(SECOND,’2013-01-14 10:59:10′, ‘2013-01-17 11:50:34’);
#262284

The result, 262284, is the real seconds between the two dates. Of course, raw seconds makes it more difficult to compute days, weeks, or even years, but you will have a more accurate answer if you put in the work. The extra work is the very reason why you see many DATEDIFFs being used when TIMESTAMPDIFF should be used.

You should definitely see the difference between the arguments of DATEDIFF vs TIMESTAMPDIFF as they are opposite. I know I was a little sneaky flipping them without telling you, but the comparison is reversed between the functions. I have no idea why they are reversed, but the creators of MySQL must have felt it was important. Personally, I like the order of the DATEDIFF’s arguments as difference is the first number subtracted from the second, but who I am to judge?

I hope this helps clear up how to compare dates accurately in MySQL.