TIMESTAMP is interesting in that it can give you an easy way of keeping track of when was the last time a row was modified, with a few caveats, listed below. The tests were run on 5.1.30-community MySQL Community Server (GPL).

• By default, TIMESTAMP is NOT NULL. Inserting a NULL value causes it to store the current DATETIME;
  [sourcecode language=”sql”]
  mysql> CREATE TABLE t1 (c1 TIMESTAMP);
  Query OK, 0 rows affected (0.11 sec)

  mysql> INSERT INTO t1 VALUES (NULL);
  Query OK, 1 row affected (0.03 sec)

  mysql> SELECT * FROM t1;
  +———————+
  | c1 |
  +———————+
  | 2008-12-30 20:42:25 |
  +———————+
  1 row in set (0.00 sec)

  mysql> CREATE TABLE t2 (c1 TIMESTAMP, c2 TIMESTAMP);
  Query OK, 0 rows affected (0.06 sec)

  mysql> INSERT INTO t2 VALUES (NULL, NULL);
  Query OK, 1 row affected (0.02 sec)

  mysql> SELECT * FROM t2;
  +———————+———————+
  | c1 | c2 |
  +———————+———————+
  | 2008-12-30 21:02:04 | 2008-12-30 21:02:04 |
  +———————+———————+
  1 row in set (0.00 sec)
  [/sourcecode]

• TIMESTAMP falls between ‘1970-01-01 00:00:01’ and ‘2038-01-19 03:14:07′, according to Paul DuBois’ excellent MySQL 4th Edition book. Testing on one of my machines (Windows) shows that it varies a bit, but not much. Maybe this has something to do with hardware and/or operating system (Update, please see comment and my verification below, thanks to reader kimseong);
  [sourcecode language=”sql”]
  mysql> INSERT INTO t1 VALUES (‘2038-01-19 03:14:07’);
  ERROR 1292 (22007): Incorrect datetime value: ‘2038-01-19 03:14:07’ for column ‘
  c1’ at row 1
  mysql> INSERT INTO t1 VALUES (‘2038-01-19 03:14:06’);
  ERROR 1292 (22007): Incorrect datetime value: ‘2038-01-19 03:14:06’ for column ‘
  c1’ at row 1
  mysql> INSERT INTO t1 VALUES (‘2038-01-18 03:14:06’);
  Query OK, 1 row affected (0.03 sec)
  [/sourcecode]

• Only the first not null column can have the on update current_timestamp property. Remember, by default, timestamp is not null;
  [sourcecode language=”sql”]
  mysql> CREATE TABLE t1(c1 INT, c2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, c3 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
  ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

  mysql> CREATE TABLE t3 (c1 TIMESTAMP, c2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

  ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP
  column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
  mysql> CREATE TABLE t3 (c1 TIMESTAMP NULL, c2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
  Query OK, 0 rows affected (0.06 sec)

  mysql> CREATE TABLE t4 (c1 TIMESTAMP NULL, c2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c3 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
  ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
  [/sourcecode]

• With the on update current_timestamp property of a timestamp column, one can use it to determine when a particular row was last modified. However, if the modification simply resets a column to its current value (essentially no data change), that particular timestamp column will not be updated.
  [sourcecode language=”sql”]
  mysql> CREATE TABLE t6 (c1 INT, c2 CHAR(2), c3 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
  Query OK, 0 rows affected (0.06 sec)

  mysql> INSERT INTO t6 VALUES (1, ‘a’, null);
  Query OK, 1 row affected (0.01 sec)

  mysql> SELECT * FROM t6;
  +——+——+———————+
  | c1 | c2 | c3 |
  +——+——+———————+
  | 1 | a | 2008-12-30 21:37:20 |
  +——+——+———————+
  1 row in set (0.00 sec)

  mysql> UPDATE t6 SET c2 = ‘b’ WHERE c1 = 1;
  Query OK, 1 row affected (0.03 sec)
  Rows matched: 1 Changed: 1 Warnings: 0

  mysql> SELECT * FROM t6;
  +——+——+———————+
  | c1 | c2 | c3 |
  +——+——+———————+
  | 1 | b | 2008-12-30 21:38:12 |
  +——+——+———————+
  1 row in set (0.00 sec)

  mysql> UPDATE t6 SET c2 = ‘b’ WHERE c1 = 1;
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 1 Changed: 0 Warnings: 0

  mysql> SELECT * FROM t6;
  +——+——+———————+
  | c1 | c2 | c3 |
  +——+——+———————+
  | 1 | b | 2008-12-30 21:38:12 |
  +——+——+———————+
  1 row in set (0.00 sec)
  [/sourcecode]