go top

MEGA Sale

  • Colection of 65 PHP scripts for $4.29 each

Get 65 PHP scripts in a bundle for $4.29 each!

View Offer

Compare Dates using MySQL Functions

Thursday, 17th January, 2013  /  MySQL Tutorials  /  0 Comments
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.
Share on:

0 Comments to "Compare Dates using MySQL Functions"

Add your comment

Captcha

    Please, be polite and helpful and do not spam or offend others! We promise you will be treated the same way!

    Log in to your account to post your comments. If you still haven't joined our community yet, you can create your FREE account now!

    Posting tip:
    If you use code in your comments, please put it in these tags [php], [sql], [css], [js] PHP code example: [php] echo date("Y-m-d"); [/php]

    Thank you,
    PHPJabbers Team

    PHP Scripts

    Check our extensive collection of top-notch PHP Scripts that will enhance your website!


    Commercial PHP scripts

    Free Scripts

    Add great new functionalities to your website with our Free Scripts collection.


    Free scripts