What are the pros and cons of the various date/time field types in MySQL?

Question

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?

Answer 1

Well I guess the following would help clarify.

Date and time can be stored in a DATETIME field in mysql.

TIMESTAMP is used if you wish to timestamp when a row was created - the field will be automatically filled in on creation.

Using an integer for a Date is slightly overkill since this is essentially what DATETIME does but does all the time consuming conversions for you.

Is there a particular benefit or drawback you are interested in finding out about?

Answer 2

Timestamp in mysql can be very tricky. If not declared carefully you may end up with field that automatically changes its value on every row update (even if you dont update it explicitly).

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

Answer 3

I would save data using the DATETIME or DATE fields in MySQL. At least, if you are going to store date values up to the year 2038: http://en.wikipedia.org/wiki/Year_2038_problem. If you're on a system that stores integers differently, you may not have this issue.

It is still easy to compare date values or even timestamps.

SELECT * FROM myTable WHERE startDate > '2009-01-01'
SELECT * FROM myTable WHERE UNIX_TIMESTAMP(startDate) > 1232541482

Answer 4

If you need millisecond fidelity for a timestamp, you need to save it as an Integer. Use caution though, this can complicate things.

Answer 5

• 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.