I know this should be pretty basic, but I'm a little confused. curdate() and now() are supposed to give the current time and date. Yet, when I use both seperately in the same exact statement I sometimes get different results. I tried looking this up, but couldn't find an answer. Anyone know why?

Thanks,
Lord Rogaine

    LordRogaine wrote:

    curdate() and now() are supposed to give the current time and date.

    Hmm.. actually, no, they aren't.

    If you'll look on the MySQL manual page (by the way - I assume we're talking about a MySQL DB?) for the Date and Time Functions, you'll see that NOW() returns a datetime value in the form of 'YYYY-MM-DD HH:MM:SS' whereas CURDATE() simply returns the date (CURDATE/b, not CURDATETIME/b, right?) in the eform of 'YYYY-MM-DD'.

    Of course, if you were ever questioning these two functions, you of course did something like this, right?: 

    mysql> SELECT NOW(), CURDATE();
+---------------------+------------+
| NOW()               | CURDATE()  |
+---------------------+------------+
| 2007-02-07 22:39:20 | 2007-02-07 |
+---------------------+------------+

      I see the difference but this is the coding I have been using and it still does not make sense to me.

      let's say for this way of writing the statement I get a result of 100.

      SELECT distinct count(username) FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(last_login) <= 1;

      If I rewrite it like this (below) still using now I will get a lesser number of results, let's say 75.

      SELECT distinct count(username) FROM table WHERE last_login >= DATE_SUB(NOW(), INTERVAL 1 DAY);

      But if I rewrite the last statement using CURDATE like (below) I will get the result of 100 like the original statement

      SELECT distinct count(username) FROM table WHERE last_login >= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

      Hope this makes sense. Curious to know why now() isn't giving consistant results in both versions of the same select statemet.

        Because when you use DATE_SUB with NOW(), you're introducing a time element (not just date).

        Using NOW() with a format-preserving function like DATE_SUB(), if I logged in yesterday at 3pm, and you ran that query today at 3:30pm, it would not return my row.

        When you compare dates using CURDATE() or TO_DAYS(), you leave off the time element and just consider whole days. As such, if you ran the script today at 3:30pm (same scenario as above), my row WOULD be returned because you're looking at all of yesterday (midnight to midnight), not yesterday at 3pm through today at 3pm.

        Does that make sense? Not sure how well I am at explaining it...

          Note to MySQL AB: suggest setting up a user forum.

            Note to MySQL AB: suggest setting up a user forum.

            Looks like they're ahead of php.net on that:

            MySQL Forums

              Write a Reply...