Timestamps in tables please

As MySQL DBA's, it helps us to understand when rows were added in a table and when those rows were last updated. As a result, we're requesting that all tables have the following two columns in them to help us track those changes:

, created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This should help when it comes time to maintain rows in all your tables because the database knows how to handle these two columns by itself given the right scenario:

mysql> CREATE TABLE foo (
->     foo_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY
->   , bar varchar(24) NOT NULL DEFAULT ''
->   , created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
->   , updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT foo ( bar, created, updated )
-> VALUES ( 'hello', NULL, NULL )
->      , ( 'world', NULL, NULL )
->      ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Notice here that we intentionally insert NULL values into the created and updated columns. These will be rows 1 and 2 below.

mysql> INSERT foo ( bar ) VALUES ( 'hello again' ) ;
Query OK, 1 row affected (0.00 sec)

This time, I'm not setting the created field at all so you can see what happens when we skip it. I'm not crazy about this inconsistency, but it's what we have to work with for now.

mysql> UPDATE foo SET bar = 'from here' WHERE foo_id = 2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM foo ;
+--------+-------------+---------------------+---------------------+
| foo_id | bar         | created             | updated             |
+--------+-------------+---------------------+---------------------+
|      1 | hello       | 2010-05-13 15:23:01 | 2010-05-13 15:23:01 |
|      2 | from here   | 2010-05-13 15:23:01 | 2010-05-13 15:23:19 |
|      3 | hello again | 0000-00-00 00:00:00 | 2010-05-13 15:23:03 |
+--------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

Notice that row 3's created column has the 0000-... default value in it. Rows 1 and 2 don't because we actually inserted the NULL value into the NOT NULL column. That demonstrates that inserting NULL into a TIMESTAMP column created with NOT NULL will cause the column to use the current time when an action is performed (INSERT/UPDATE/REPLACE). This does not work with DATE, TIME, and DATETIME fields.

Want to learn more? Check out this link.