MySQL TIMESTAMP Data Type - Use With Care

Just to share what i encountered with the TIMESTAMP data type in MySQL, so that you don't make the same wrong assumptions.

First, let's create a table with a DATETIME column, and insert a record into the table.

mysql> CREATE TABLE TBL_TEST_DATETIME (ID INTEGER, TEST_DT DATETIME, PRIMARY KEY (ID));
mysql> COMMIT;
mysql> INSERT INTO TBL_TEST_DATETIME (ID) VALUE (0);
mysql> COMMIT;

Notice that i did not specify a value for the TEST_DT column as i wanted it to take a NULL value. Let's query to see the record that was added.

mysql> SELECT * FROM TBL_TEST_DATETIME WHERE ID = 0;
+----+---------+
| ID | TEST_DT |
+----+---------+
|  0 | NULL    |
+----+---------+
1 row in set (0.00 sec)

So far so good. Now we'll repeat the test, but using the TIMESTAMP datatype instead of DATETIME.

mysql> DROP TABLE TBL_TEST_DATETIME;
mysql> COMMIT;

mysql> CREATE TABLE TBL_TEST_TIMESTAMP (ID INTEGER, TEST_TMS TIMESTAMP, PRIMARY KEY (ID));
mysql> COMMIT;
mysql> INSERT INTO TBL_TEST_TIMESTAMP (ID) VALUE (0);
mysql> COMMIT;

mysql> SELECT * FROM TBL_TEST_TIMESTAMP WHERE ID = 0;
+----+---------------------+
| ID | TEST_TMS            |
+----+---------------------+
|  0 | 2010-01-24 01:37:14 |
+----+---------------------+
1 row in set (0.00 sec)

That's a surprise. We did not specify a value for the TEST_TMS column but still it was populated. Maybe we need to explicitly specify NULL for the value.

mysql> INSERT INTO TBL_TEST_TIMESTAMP (ID, TEST_TMS) VALUE (1, NULL);
mysql> COMMIT;

mysql> SELECT * FROM TBL_TEST_TIMESTAMP WHERE ID = 1;
+----+---------------------+
| ID | TEST_TMS            |
+----+---------------------+
|  1 | 2010-01-24 01:40:49 |
+----+---------------------+
1 row in set (0.00 sec)

Well, the TEST_TMS column still got populated with a value even though we explicitly specified NULL in the insert statement.

Note that this was bewildering to me because when i created the table, i did not specify any default value for the the TEST_TMS column, which was just like how i created the TBL_TEST_DATETIME table in the first test.

Of course, i did not count on the fact that MySQL handles a TIMESTAMP column differently from a DATETIME column.

From the MySQL 5.5 Reference Manual (TIMESTAMP Properties):

TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp. However, a TIMESTAMP column can be allowed to contain NULL by declaring it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is illegal.) If a TIMESTAMP column allows NULL values, assigning NULL sets it to NULL, not to the current timestamp.

That explains all we have observed in the second test. Let's confirm it.

mysql> DROP TABLE TBL_TEST_TIMESTAMP;
mysql> COMMIT;

mysql> CREATE TABLE TBL_TEST_TIMESTAMP (ID INTEGER, TEST_TMS TIMESTAMP NULL, PRIMARY KEY (ID));
mysql> COMMIT;
mysql> INSERT INTO TBL_TEST_TIMESTAMP (ID) VALUE (0);
mysql> COMMIT;

mysql> SELECT * FROM TBL_TEST_TIMESTAMP WHERE ID = 0;
+----+----------+
| ID | TEST_TMS |
+----+----------+
|  0 |     NULL |
+----+----------+
1 row in set (0.00 sec)

There we have it. Remember to explicitly declare a TIMESTAMP column with NULL if you intend for it to hold NULL values.

HTH.