How do I store dates in MySQL?

[chinmay]

Human-readable dates come in a variety of formats that can suit many situations. However, these formats are not the best way to store dates.

At first glance, the easiest way to store dates in MySQL may appear to be to simply drop them in exactly as they’d appear on a web page; for example, “8th March 2003”. Be warned—taking this route is the first step on the path to serious hair loss and ulcers. For example, the WHERE clause in an SQL statement run against MySQL will not allow you to do things like this:

SELECT * FROM table WHERE date > '14th February 2007

'14th February 2007'is not a date value—it’s only a date represented by a string. It can’t be manipulated or compared as a date value until it is converted into such a value. If you store your dates as strings you’ll be forever converting them to and from date value data types. And who needs that kind of headache?

Solution

A far better way to store date information is to use a MySQL timestamp.
To get the current time, in the current server’s local timezone, we can use the NOW or CURRENT_TIMESTAMP functions. We can also use the UTC_TIMESTAMP to obtain the UTC timezone timestamp:

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2007-11-05 21:18:28 |
+---------------------+
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-11-05 21:18:32 |
+---------------------+
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2007-11-06 02:18:44 |
+---------------------+