Compare Dates using MySQL Functions

Comparing dates can be a difficult challenge for any programmer. With MySQL, we have the awesome benefit of a few 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 them in the difference of days and the difference of seconds.

SELECT DATEDIFF('2013-01-14 10:59:10','2013-01-17 11:50:34')*24*60*60;

# returns 259200

The MySQL DATEDIFF function is not really the best function in the world. The trailing "*24*60*60" are used because DATEDIFF only returns dates. But, we wanted the result in seconds so we multiplied by hours of a day, minutes in an hour, and seconds in a minute. In the next example you will see why I have done this and why the MySQL TIMESTAMPDIFF is much more accurate. The DATEDIFF can be deceiving when I put in full date times because DATEDIFF doesn't care about the time string (ex: "10:59:10"). It only looks at the dates. So, 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 just as we expect. That is the number difference of days in seconds, but not the difference of seconds between the two datetimes. The MySQL TIMESTAMPDIFF on the other hand can do a datetime second comparison:

SELECT TIMESTAMPDIFF(SECOND,'2013-01-14 10:59:10', '2013-01-17 11:50:34');

# returns 262284

The result, 262284, is the real seconds between the two dates. At least these are real and true seconds, unlike the MySQL DATEDIFF where they are just a conversion of days to seconds. You would have to modify this with some other functions or operators to get values other than seconds. Ultimately, you should use your best judgement to determine which function is better for your task.