MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Timestamp Columns

with 4 comments

Somebody asked how to work around an error message they got after converting one of their who-audit columns to a TIMESTAMP column. A TIMESTAMP column has a DEFAULT or ON UPDATE current timestamp. They wanted to have two TIMESTAMP columns in the same table, with the intention of:

  1. Having the created column assign a current timestamp value on insert
  2. Having the updated column assign a current timestamp value on insert and update

This is a sample table with two of the traditional four who-audit columns:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    TIMESTAMP NOT NULL 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

When they tried to create the table they got the following error:

ERROR 1293 (HY000): Incorrect TABLE definition; there can be ONLY one TIMESTAMP COLUMN WITH CURRENT_TIMESTAMP IN DEFAULT OR ON UPDATE clause

While you can define a table with two columns that have a TIMESTAMP data type, you can’t define a table with two TIMESTAMP columns when one holds a DEFAULT or ON UPDATE CURRENT_TIMESTAMP value. However, you can use a DATETIME data type for the created column provided it’s null allowed, like:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    DATETIME 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

You can then define an on insert database trigger that fires when you create a new row, like this:

CREATE TRIGGER sample_t
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
  SET NEW.created = CURRENT_TIMESTAMP();
END;
$$

An INSERT statement would look like this:

INSERT INTO sample VALUES (NULL, NULL, NULL);

You would see the following if you query the table:

+-----------+---------------------+---------------------+
| sample_id | created             | updated             |
+-----------+---------------------+---------------------+
|         1 | 2011-08-11 00:26:21 | 2011-08-11 00:26:21 |
+-----------+---------------------+---------------------+

Hope this helps other too.

Written by maclochlainn

August 11th, 2011 at 1:31 am

4 Responses to 'MySQL Timestamp Columns'

Subscribe to comments with RSS or TrackBack to 'MySQL Timestamp Columns'.

  1. “While you can define a table with two columns that have a TIMESTAMP data type, you can’t define a table with one TIMESTAMP column and another column with a TIMESTAMP data type.”

    I’m not sure I understand this exactly. You can have two fields with a data type of TIMESTAMP. So what is the TIMESTAMP column you’re referring to that prevents having another field be that data type?

    Marc

    18 Jul 12 at 3:15 pm

  2. Yes, great catch! That was badly worded. I’ve fixed it in the posting.

    maclochlainn

    19 Jul 12 at 2:44 pm

  3. how to create a table with TIMESTAMP and DATETIME value by doing on mysql query browser…epecially while assigning DEFAULT CURRENT_TIMESTAMP and on update_TIMESTAMP.

    parimal

    4 Oct 12 at 12:24 am

  4. If created is not null, then execute

    SET NEW.created = CURRENT_TIMESTAMP();

    I have entered a date and time in which I am importing posts from WordPress and I don’t want the trigger to be executed. How would I do that?

    Grayson Peddie

    24 Apr 14 at 7:34 pm

Leave a Reply