MySQL – Should I Use DATETIME or TIMESTAMP Data Type?
datetimemysqlsqldatatypestimestamp
Would you recommend using a datetime or a timestamp field, and why (using MySQL)?
I'm working with PHP on the server side.
Best Answer
Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
Also, as of MySQL 8.0.19 the DATETIMEsupports time zone offsets, so there's even less reason to use TIMESTAMP now.
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.
DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.
DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
TIMESTAMP: It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.
TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
Best Answer
Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native
DATETIME
format. You can do calculations within MySQL that way("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)")
and it is simple to change the format of the value to a UNIX timestamp("SELECT UNIX_TIMESTAMP(my_datetime)")
when you query the record if you want to operate on it with PHP.Also, as of MySQL 8.0.19 the
DATETIME
supports time zone offsets, so there's even less reason to useTIMESTAMP
now.