Interesting things about TIMESTAMP data type in MySQL

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;
    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)
    
  • 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);
    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)
    
  • Only the first not null column can have the on update current_timestamp property. Remember, by default, timestamp is not null;
    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
    
  • 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.
    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)
    

8 thoughts on “Interesting things about TIMESTAMP data type in MySQL”

  1. TIMESTAMP falls between ‘1970-01-01 00:00:01′ and ‘2038-01-19 03:14:07′

    This is probably due to the timezone, since timestamp is timezone aware and is stored in the UTC timezone value. So, your testing of the limit might not be accurate due to your timezone settings.

  2. You are exactly right kimseong. Thanks a lot!

    Here is some further testing I did. I live in Chicago area, so it is 6 hours behind the UTC. I adjusted the limit to be 6 hours behind and got the expected results.

    mysql> create table t1 (c1 timestamp);
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> insert into t1 values ('1969-12-31 18:00:01');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from t1;
    +---------------------+
    | c1                  |
    +---------------------+
    | 1969-12-31 18:00:01 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into t1 values ('1969-12-31 17:59:59');
    ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 17:59:59' for column 'c1' at row 1
    mysql> insert into t1 values ('2038-01-18 21:14:07');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from t1;
    +---------------------+
    | c1                  |
    +---------------------+
    | 1969-12-31 18:00:01 |
    | 2038-01-18 21:14:07 |
    +---------------------+
    2 rows in set (0.00 sec)
    
    mysql> insert into t1 values ('2038-01-18 21:14:08');
    ERROR 1292 (22007): Incorrect datetime value: '2038-01-18 21:14:08' for column 'c1' at row 1
    
  3. Nice post – don’t you love default behaviours? 🙂

  4. Cheer James.

    Long time no see my friend. Oh yeah, the lazy boy style defaults suit me just fine…

  5. **By default, TIMESTAMP is NOT NULL**

    When you do CREATE TABLE you are not specifying the possibility of NULL values. So of course you won’t be able to save a NULL time stamp.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.