You are here: Home / Technologies / Databases / DateTime vs Timestamp

DateTime vs Timestamp

by

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!

Comments

  3. Erik says

    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.

  6. PJ says

    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

  7. says

    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;