What are the advantages and disadvantages of different types of date / time fields in MySQL?

advertisements

Date and time in MySQL can be stored as DATETIME, TIMESTAMP, and INTEGER (number of seconds since 01/01/1970). What are the benefits and drawbacks of each, particularly when developing under a LAMP stack?


  • TIMESTAMP is stored in a MySQL proprietary method (though it's basically just a string consisting of year, month, day, hour, minutes and seconds) and additionally, a field of type TIMESTAMP is automatically updated whenever the record is inserted or changed and no explicit field value is given:

    mysql> create table timestamp_test(
        id integer not null auto_increment primary key,
        val varchar(100) not null default '', ts timestamp not null);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into timestamp_test (val) values ('foobar');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from timestamp_test;
    +----+--------+----------------+
    | id | val    | ts             |
    +----+--------+----------------+
    |  1 | foobar | 20090122174108 |
    +----+--------+----------------+
    1 row in set (0.00 sec)
    
    mysql> update timestamp_test set val = 'foo bar' where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from timestamp_test;
    +----+---------+----------------+
    | id | val     | ts             |
    +----+---------+----------------+
    |  1 | foo bar | 20090122174123 |
    +----+---------+----------------+
    1 row in set (0.00 sec)
    
    mysql>
    
    
  • DATETIME is the standard data type for dates and times which works in conjunction with the date and time functions in MySQL. I'd probably use this in practice

  • Storing dates in INTEGER format is not recommended, as you are opening a real can of worms due to interesting problems like time zones, leap years and the like - at least if you intend to query the database based on specific dates stored in that field.