How to create a TIMESTAMP/DATETIME column in MySQL to automatically be set with the current time

The TIMESTAMP data type is the only way to have MySQL automatically set the time when a row was inserted and/or updated. DATETIME columns can’t do this.

TIMESTAMP columns are identical to DATETIME columns with one important exception — they can be set to take the current time value when a row is created or updated.

You can define more than one TIMESTAMP colum in a table — however, only ONE TIMESTAMP column in a table can be configured for Auto-Update or Auto-Initialization.

(For that reason, I usually make a practice of setting other columns to ‘DATETIME’ so there’s only one ‘TIMESTAMP’ column per table.)

There are four options when using TIMESTAMP, they are:

  • Auto-initialization and auto-update:
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • Auto-initialization only:
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • Auto-update only:
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
  • Neither:
  ts TIMESTAMP DEFAULT 0

Here’s an example of a complete ‘CREATE TABLE’ statement that shows how it all works:

CREATE TABLE `foo`.`timestamp_example` (
`id`                  INTEGER(10) UNSIGNED AUTO_INCREMENT,
`some_text_field`     VARCHAR(20),
`updated_at`          TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_at`          DATETIME DEFAULT NULL,
PRIMARY KEY (id)
);

Note that only one of the fields can be TIMESTAMP, that’s a limitation of MySQL.

The choice to use the TIMESTAMP on the ‘updated_at’ field was random in this example. Choose whatever makes sense for your application.

Click here to go to the table of contents for all MySQL Programming Example Code

This entry was posted in mysql and tagged , . Bookmark the permalink.
  • http://farinspace.com Dimas

    My initial thinking was along the same lines as yours: having a single timestamp value and the others as datetime. My thinking has since changed to having all datetime values as TIMESTAMP fields because: depending on your development environments it will not always be guaranteed that the timezone values set for deployment servers will be similar, equally the changing of timezone values can screw up the datetime inputs into your tables.

    Saving as DATETIME means that you are saving the datetime value in the current operating timezone.

    Saving as TIMESTAMP means that MySQL will convert the datetime from the operating timezone into UTC (Unix Time) and store it, then convert it back to the operating timezone on retrieval. Using TIMESTAMP fields makes avoids confusion and allows you to accurately display with out the worrying which timezone the datetime is stored as in the database.

    Current score: 0
  • http://fensterkitt.ch FroDo

    @Diamas: You are perfectly right – I think the fact that the data in a timestamp row is stored in UTC is a even more more important feature of this data type than the auto update function!

    Current score: 0
  • http://www.bigsoft.co.uk/ davidnewcomb

    “Note that only one of the fields can be TIMESTAMP, that’s a limitation of MySQL.”

    This is not true. You can have as many TIMESTAMP columns as you like in a table. The only limitation is that the first TIMESTAMP column in the table is updated when the record is updated and the other TIMESTAMP columns are not.

    Current score: 0
  • Donald W Long

    You can use triggers see below:

    CREATE TABLE `MyTable` (
    `MyTable_ID` int UNSIGNED NOT NULL AUTO_INCREMENT ,
    `MyData` varchar(10) NOT NULL ,
    `CreationDate` datetime NULL ,
    `UpdateDate` datetime NULL ,
    PRIMARY KEY (`MyTable_ID`)
    )
    ;

    CREATE TRIGGER `MyTable_INSERT` BEFORE INSERT ON `MyTable`
    FOR EACH ROW BEGIN
    — Set the creation date
    SET new.CreationDate = now();

    — Set the udpate date
    Set new.UpdateDate = now();
    END;

    CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
    FOR EACH ROW BEGIN
    — Set the udpate date
    Set new.UpdateDate = now();
    END;

    Current score: 0
  • Shane Harter

    I know this is old, but just in case people find this on Google the same way I did: Donald W Long is correct, you CAN you Triggers. But often, you shouldn’t. If you read the docs (or High Performance MySQL) you’ll realize how much of a performance impact triggers have. Not to mention you can argue that they increase application complexity by spreading out your application logic into the storage tier. 

    Current score: 0
  • guest

    Great post.  It was super useful.
    Cheers

    Current score: 0
  • Aseire

    Nice article, clearly written – personally I opted to programmatically set the TIMESTAMP  as I have three TIMESTAMP columns where I am benchmarking a compute process as it proceeds.

    Current score: 0
  • http://twitter.com/horsebones Leke

    Thanks bro. This was very easy to follow and helped me with my table creation.

    Current score: 0
  • Anonymous

    Thank you for this. I needed to know how to do exactly this.

    Current score: 0
  • Jonathan Cross

    Perfect, thanks for this!

    >> TIMESTAMP columns are identical to DATETIME…
    There is some small, but critical differences between DATETIME and TIMESTAMP eg:

    “MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)”

    http://dev.mysql.com/doc/refman/5.7/en/datetime.html

    Current score: 0
  • ugurcmk

    Thank you. I need this

    Current score: 0
  • http://www.coscms.org/ dennis

    Nice one. Easy to implement in all tables. And no messing around with PHP or another language :)

    Current score: 0
  • Mike3

    at last someone has told us all. Thanks.

    Current score: 0