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();
CODE:
+————+
| 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);
CODE:
+————————————–+
| 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.
CODE:
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();
CODE:
+———————+
| NOW() |
+———————+
| 2004-11-30 18:00:34 |
+———————+
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);
CODE:
+———————+———————————-+
| 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.