Exploring Mysql CURDATE and NOW. The same but different.
Sometimes I see people attempting to use VARCHARS or CHARS to store dates in their MySQL database application. This is really fighting against MySQL, which has a variety of interchangeable date types. Internally MySQL is storing dates as numbers, which allows it to do all sorts of nice arithmetic and comparisons with very little effort on your part. For example, when you use a mysql DATE column to store a date, you don't have to worry about sortation, or comparing that date to another DATE, because MySQL already understands how to do those things internally. A lot of people also don't realize that they can output a DATE column in just about any way they choose using the DATE_FORMAT function. This causes people to shy away from using DATE, DATETIME, TIME, or TIMESTAMP columns, when they really should.
We can break down mysql DATE columns into 3 categories:
DATE: Has no Time component.
DATETIME and TIMESTAMP: Has both Date and Time component.
TIME: Has only Time component.
I tend to use DATETIME most often, and occassionally will use TIMESTAMP. I'll talk about TIMESTAMP another day, and explain the pros and cons of using it.
In this entry I'm going to concentrate on two very important MySQL date functions: CURDATE() and NOW(). Take a look at these examples, and keep in mind that the semicolons are added to terminate the SQL statements as you would have to if you were using the mysql client application. If you're using phpMyAdmin you don't need the semicolons.
+------------+
| CURDATE() |
+------------+
| 2004-11-30 |
+------------+
1 row in set (0.00 sec)
CURDATE() returns you the date part of the mysql server's datetime. The server gets this from the operating system, so basically it's whatever the Date/Time is on the machine that is running your mysql server.
Notice that CURDATE() as its name implies has no TIME component. Let's assume that what your application needs to do is find out what date it was "yesterday". If Time really isn't important, then CURDATE is the way to go, as it's not concerned with TIME.
Although it's not that intuitive, the way to get "YESTERDAY" is to use the DATE_ADD() function. The INTERVAL component allows us to Add a -1 DAY
+--------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -1 DAY) |
+--------------------------------------+
| 2004-11-29 |
+--------------------------------------+
1 row in set (0.00 sec)
This is not to say that you can't then use the DATE to compare against a DATETIME. You can! What happens is that mysql assumes for comparison purposes that your DATE is the equivalent to the "first second of that day" or that day at 12:00 am.
This little experiment illustrates the idea.
mysql> create table onedate (onedate DATETIME);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into onedate VALUES(NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from onedate where onedate >= CURDATE();
+---------------------+
| onedate |
+---------------------+
| 2004-11-30 17:52:01 |
+---------------------+
1 row in set (0.00 sec)
Notice that a row was returned, because CURDATE() will always be less than or equal to NOW().
Compare CURDATE() with NOW(). Like CURDATE(), NOW() returns you the system DATE but also includes the time component. If you need TIME in your application, tracking logins, or the date and time a message was entered by a user, then you need a mysql DATETIME rather than a MYSQL date, or you will not be able to capture the important time component.
mysql> SELECT * FROM onedate WHERE onedate >= CURDATE();
+---------------------+
| onedate |
+---------------------+
| 2007-11-14 22:13:03 |
+---------------------+
1 row in set (0.00 sec)
Notice the 24 Hour time component.
Putting a nail in the issue, here's the same DATE_ADD function called against NOW().
+---------------------+----------------------------------+
| NOW() | DATE_ADD(NOW(), INTERVAL -1 DAY) |
+---------------------+----------------------------------+
| 2004-11-30 18:07:17 | 2004-11-29 18:07:17 |
+---------------------+----------------------------------+
1 row in set (0.00 sec)
What we get is exactly the same time, yesterday.
The intrinsic DATE types in a relational database are always the way to go when you need to handle date and time in your application. Let the database do the heavy lifting, and you will make your application faster, more reliable and easier to maintain in the long run.
DATE: Has no Time component.
DATETIME and TIMESTAMP: Has both Date and Time component.
TIME: Has only Time component.
I tend to use DATETIME most often, and occassionally will use TIMESTAMP. I'll talk about TIMESTAMP another day, and explain the pros and cons of using it.
In this entry I'm going to concentrate on two very important MySQL date functions: CURDATE() and NOW(). Take a look at these examples, and keep in mind that the semicolons are added to terminate the SQL statements as you would have to if you were using the mysql client application. If you're using phpMyAdmin you don't need the semicolons.
+------------+
| CURDATE() |
+------------+
| 2004-11-30 |
+------------+
1 row in set (0.00 sec)
CURDATE() returns you the date part of the mysql server's datetime. The server gets this from the operating system, so basically it's whatever the Date/Time is on the machine that is running your mysql server.
Notice that CURDATE() as its name implies has no TIME component. Let's assume that what your application needs to do is find out what date it was "yesterday". If Time really isn't important, then CURDATE is the way to go, as it's not concerned with TIME.
Although it's not that intuitive, the way to get "YESTERDAY" is to use the DATE_ADD() function. The INTERVAL component allows us to Add a -1 DAY
+--------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -1 DAY) |
+--------------------------------------+
| 2004-11-29 |
+--------------------------------------+
1 row in set (0.00 sec)
This is not to say that you can't then use the DATE to compare against a DATETIME. You can! What happens is that mysql assumes for comparison purposes that your DATE is the equivalent to the "first second of that day" or that day at 12:00 am.
This little experiment illustrates the idea.
mysql> create table onedate (onedate DATETIME);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into onedate VALUES(NOW());
Query OK, 1 row affected (0.00 sec)
mysql> select * from onedate where onedate >= CURDATE();
+---------------------+
| onedate |
+---------------------+
| 2004-11-30 17:52:01 |
+---------------------+
1 row in set (0.00 sec)
Notice that a row was returned, because CURDATE() will always be less than or equal to NOW().
Compare CURDATE() with NOW(). Like CURDATE(), NOW() returns you the system DATE but also includes the time component. If you need TIME in your application, tracking logins, or the date and time a message was entered by a user, then you need a mysql DATETIME rather than a MYSQL date, or you will not be able to capture the important time component.
mysql> SELECT * FROM onedate WHERE onedate >= CURDATE();
+---------------------+
| onedate |
+---------------------+
| 2007-11-14 22:13:03 |
+---------------------+
1 row in set (0.00 sec)
Notice the 24 Hour time component.
Putting a nail in the issue, here's the same DATE_ADD function called against NOW().
+---------------------+----------------------------------+
| NOW() | DATE_ADD(NOW(), INTERVAL -1 DAY) |
+---------------------+----------------------------------+
| 2004-11-30 18:07:17 | 2004-11-29 18:07:17 |
+---------------------+----------------------------------+
1 row in set (0.00 sec)
What we get is exactly the same time, yesterday.
The intrinsic DATE types in a relational database are always the way to go when you need to handle date and time in your application. Let the database do the heavy lifting, and you will make your application faster, more reliable and easier to maintain in the long run.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded
brainv on :
but, how can i select now() with like using curdate()
+---------------------+
| last_date |
+---------------------+
| 2005-10-12 11:05:14 |
| 2005-10-12 00:00:00 |
+---------------------+
i triend,
mysql> select * from tbl where last_date like 'now()%';
but it give me error. any suggestion?
David on :
If you really want to turn things into strings and compare them, you can using DATE_FORMAT, but that's usually not the best solution.
Prince on :
will show reading of current date
Sean Budlong on :
peppe on :
How can i display different random record every 24 hours
Cat I do something like this
SELECT * FROM tbl ORDER BY RAND(DATE_FORMAT(CURTIME(), '%T')) LIMIT 0,1
Jessica Miller on :
so something like this:
SELECT * FROM $table WHERE $day >= CURDATE() AND $day < CURDATE() + 7 ORDER BY id ASC
Lisa on :
imawesty on :
otomo on :
How to Query them
Myra on :
I'd been using curdate()-1 or curdate()+9 and now that I noticed that casts it to a string or something horrible, after seeing that it wasn't very reliable with ends of months and such, I was looking for the right way to do it. Nice article!
joie on :
Darwination on :
devon on :
What happens if the date is on the 31st?
31+7?
there's no 38th.
I wanna display stuff for the following week! HELP!
David on :
Richard Cairns on :
samban on :
(The problem is that this function returns our query itself as the output with the date.)
I want to store it in a table ......!
Can anyone please explain ........???
I'm in big trouble........!