Exploring Mysql CURDATE and NOW. ...

GizmoLA.com



Exploring Mysql CURDATE and NOW. The same but different. Tue, Nov 30. 2004

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.

SELECT CURDATE();



+------------+
| 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

SELECT DATE_ADD(CURDATE(), INTERVAL -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.

SELECT * FROM onedate WHERE onedate >= CURDATE();



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().

SELECT NOW(), DATE_ADD(NOW(), INTERVAL -1 DAY);



+---------------------+----------------------------------+
| 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.
Defined tags for this entry: , , , , , , , , ,
Related entries by tags:
Posted by David Rolston in Web Tech Comments: (16) Trackbacks: (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

#1 - brainv said:
2005-10-11 20:30 - (Reply)

*hi nice tips!

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?

#1.1 - David 2007-11-14 21:50 - (Reply)

*Sorry I couldn't really understand what you wanted to do from the question. Again, Date type columns are not strings, so a LIKE query is not appropriate. You would instead probably want to do a greater than or less than comparison relative to CURDATE.

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.

#1.2 - Prince 2009-01-06 20:53 - (Reply)

*select * from tbl_name where dateofrequest > curdate();


will show reading of current date

#2 - Sean Budlong said:
2005-12-21 10:06 - (Reply)

*David, thank you for this very helpful article. I'm going to experiment with it as I try to construct a query that will run every 24 hours to grab the last day's worth of data from one online MySQL database and insert it into another online MySQL database. I haven't come up with the correct syntax yet, but your explanation is a great help. Thank you very much.

#3 - peppe 2007-02-09 06:38 - (Reply)

*Hi,
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

#4 - Jessica Miller said:
2007-04-06 11:53 - (Reply)

*How can I select todays info and then the info for the next week?

so something like this:
SELECT * FROM $table WHERE $day >= CURDATE() AND $day < CURDATE() + 7 ORDER BY id ASC

#5 - Lisa 2007-10-27 03:37 - (Reply)

*Hi, How i can select date for the past three month

#5.1 - imawesty said:
2008-07-28 20:47 - (Reply)

*SELECT column FROM table WHERE column >= date_add(curdate(), interval -3 month);

#6 - otomo 2008-08-30 10:44 - (Reply)

*I want select day for next 15 day from current day

How to Query them

#7 - Myra 2009-01-25 17:24 - (Reply)

*Thank you!

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!

#8 - joie 2009-03-23 23:18 - (Reply)

*hi please help me on how i retrieve records that was stored for one week from mysql to php? for example I stored today so by saturday it will recall all the data stored from sunday to saturday. Then it will also reset to null or zero on the following week. Thank you!

#9 - Darwination 2009-07-21 22:19 - (Reply)

*I don't usually comment but this is a great post. Working with dates/times has to be the worst aspect of programming IME.

#10 - devon 2009-09-30 02:43 - (Reply)

*this is a great post i think im having

What happens if the date is on the 31st?
31+7?

there's no 38th.

I wanna display stuff for the following week! HELP!

#10.1 - David said:
2009-12-01 21:02 - (Reply)

*Using the techniques I discussed, MySQL's DATE_ADD works with the underlying calender, so there's no concern about adding to a particular date. It doesn't care if that's the last day of the month, as the concept of what month a particular day is, is something that's layered on during formatting.

#11 - Richard Cairns said:
2010-01-27 14:06 - (Reply)

*I just want to say thank you, dates always get me time and time again.

#12 - samban 2011-04-08 03:40 - (Reply)

*So does anyone know how to use the output of DATE_ADD() in another function????

(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........!


Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

BBCode format allowed