07
DateTime vs Timestamp
I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”
It’s a good MySQL introduction question, here are some general considerations for choosing one.
Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.
1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.
I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.
And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!
I test in mysql v5.1, TIMESTAMP is 6 bytes?
Comment by Paul Gao — September 7, 2007 @ 5:09 pm
“This enables the column to have a default value for INSERT or for UPDATE, but not both. ”
Are you sure about this? From the mysql online manual:
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Comment by Rory — September 7, 2007 @ 5:21 pm
To quote the reference manual:
A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation.
(http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html)
It is good for that purpose but should not be used for anything else because:
1) the range is limited, not only starting at 1970 but also ending somewhere in 2038, which is more relevant in many systems
2) the server might update it automatically anyhow, and this depends on if it is the first timestamp column or not. If you do use them you should always specify the default. The rules are very complicated: (http://dev.mysql.com/doc/refman/5.0/en/timestamp.html)
3) the difference in storage space is not important. I have never seem a system where the problem is to many bytes for datetime values. If your database is too large you should look at strings, BLOBs etc, too long primary keys (very important for clustered tables like in InnoDB), normalization, perhaps delete or archive old data.
Comment by Erik — September 7, 2007 @ 5:26 pm
There is a space between the date and time in the DATETIME
Comment by Richard Thomas — September 7, 2007 @ 9:40 pm
Timestamp also stores all values in UTC and converts it to whatever your @@time_zone is set to/from on retrieval/inserts…
Comment by Tobias "flupps" Asplund — September 8, 2007 @ 2:54 am
When attempting to create a table in MySQL 4.1.20 with two TIMESTAMP columns, one for modified_ts, one for created_ts, I get the following error, even though the docs say I should be able to do so.
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
What’s up with this, and how can I do what I need to do without resorting to triggers?
Thanks,
PJ
Comment by PJ — September 11, 2007 @ 2:44 am
Using MySQL 4.1, it’s not possible to have multiple columns with DEFAULT and ON UPDATE for CURRENT_TIMESTAMP. The best means is to do.
use test;
drop table if exists test1;
create table test1 (
id int unsigned not null auto_increment primary key,
c1 char(10) not null,
last_update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
create_ts TIMESTAMP);
truncate table test1;
insert into test1 (c1,create_ts) values(’a',now()), (’b',now());
select * from test1;
# 5.0 syntax select sleep(3);
update test1 set c1=’aa’ where c1=’a';
select * from test1;
Comment by Ronald — September 11, 2007 @ 4:55 am