Skip navigation links
10.3.2 The TIME Type »
Section Navigation      [Toggle]

10.3.1.2. TIMESTAMP Properties as of MySQL 4.1

In MySQL 4.1 and up, the properties of the TIMESTAMP data type changed in several ways. The following discussion describes the revised syntax and behavior.

Beginning with MySQL 4.1.3, the default current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described in Section 9.7, “MySQL Server Time Zone Support”. TIMESTAMP values still are stored in UTC, but are converted from the current time zone for storage, and converted back to the current time zone for retrieval. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

From MySQL 4.1.0 on, TIMESTAMP display format differs from that of earlier MySQL releases:

  • TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is 'YYYY-MM-DD HH:MM:SS'.

  • Display widths (used as described in the preceding section) are no longer supported. In other words, for declarations such as TIMESTAMP(2), TIMESTAMP(4), and so on, the display width is ignored.

The following items summarize TIMESTAMP initialization and updating properties prior to MySQL 4.1.2:

  • The first TIMESTAMP column in table row automatically is set to the current timestamp when the record is created if the column is set to NULL or is not specified at all.

  • The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL.

  • If a DEFAULT value is specified for the first TIMESTAMP column when the table is created, it is silently ignored.

  • Other TIMESTAMP columns in the table can be set to the current TIMESTAMP by assigning NULL to them, but they do not update automatically.

Beginning with MySQL 4.1.2, you have more flexible control over when automatic TIMESTAMP initialization and updating occur and which column should have those behaviors:

  • For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

  • Any single TIMESTAMP column in a table can be used as the one that is initialized to the current date and time, or updated automatically. This need not be the first TIMESTAMP column.

  • In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

    • With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

    • With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

    • With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

    • With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

    • With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.

    In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to enable auto-update without also having the column auto-initialized.) The following column definitions demonstrate each of the possibilities:

    • 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

  • To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs.

    Example: 

    CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);

  • CURRENT_TIMESTAMP or any of its synonyms (CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, or LOCALTIMESTAMP()) can be used in the DEFAULT and ON UPDATE clauses. They all mean “the current timestamp.” (UTC_TIMESTAMP is not allowed. Its range of values does not align with those of the TIMESTAMP column anyway unless the current time zone is UTC.)

  • The order of the DEFAULT and ON UPDATE attributes does not matter. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other. For example, these statements are equivalent: 

    CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

The following rules describe the changes in MySQL 4.1 regarding TIMESTAMP and handling of NULL values:

  • Before MySQL 4.1.2, TIMESTAMP columns are NOT NULL. They cannot contain NULL values, and assigning NULL assigns the current timestamp. Any DEFAULT clause is ignored.

  • From MySQL 4.1.2 to 4.1.5, TIMESTAMP columns are NOT NULL. They cannot contain NULL values, and assigning NULL assigns the current timestamp. A DEFAULT NULL clause can be specified, but it is treated as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column and as DEFAULT 0 for other TIMESTAMP columns.

  • As of MySQL 4.1.6, TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp. However, a TIMESTAMP column can be allowed to contain NULL by declaring it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is illegal.) If a TIMESTAMP column allows NULL values, assigning NULL sets it to NULL, not to the current timestamp.

The following table contains several TIMESTAMP columns that allow NULL values: 

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

Note that a TIMESTAMP column that allows NULL values not take on the current timestamp except under one of the following conditions:

In other words, a TIMESTAMP column defined as NULL will auto-initialize only if it is created using a definition such as the following: 

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

Otherwise — that is, if the TIMESTAMP column is defined to allow NULL values but not using DEFAULT CURRENT_TIMESTAMP, as shown here… 

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');

…then you must explicitly insert a value corresponding to the current date and time. For example: 

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

Note

Beginning with MySQL 4.1.1, the MySQL server can be run with the MAXDB SQL mode enabled. When the server runs with this mode enabled, TIMESTAMP is identical with DATETIME. That is, if this mode is enabled at the time that a table is created, TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and there is no automatic initialization or updating to the current date and time.

To enable MAXDB mode, set the server SQL mode to MAXDB at startup using the --sql-mode=MAXDB server option or by setting the global sql_mode variable at runtime: 

mysql> SET GLOBAL sql_mode=MAXDB;

A client can cause the server to run in MAXDB mode for its own connection as follows: 

mysql> SET SESSION sql_mode=MAXDB;

User Comments

Posted by Geoffrey Hoffman on December 9 2007 12:46am[Delete] [Edit]

The only way to have more than one TIMESTAMP column, when one is declared with either DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP or both, is to force the other timestamp column default to a valid timestamp value, such as 20070101000000.

I believe this is due to a TIMESTAMP column without a declared default uses the CURRENT_TIMESTAMP and thus throws this error

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

even though it appears you are only trying to use it on one column.

Posted by m m on January 27 2008 11:20am[Delete] [Edit]

nope, all you have to do is to declare TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP before any other TIMESTAMP columns.

short explanation from someone who knows why is this so, would be nice.

Posted by Dan Barlow on January 28 2008 7:56pm[Delete] [Edit]

`user_created_date` timestamp default CURRENT_TIMESTAMP COMMENT 'creation timestamp',
`user_updated_date` timestamp default '20070101000000' COMMENT 'edit timestamp',

This will work. If you try to set the second timestamp field to ON UPDATE CURRENT_TIMESTAMP it will then report
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
It seems to me the function of having both a creation timestamp set once by the default and an edit timestamp set repeatedly on update is NOT SUPPORTED by MySQL.

Posted by Michael Moussa on February 14 2008 6:09pm[Delete] [Edit]

You do not have to default it to some constant, you can also default it to NULL, like so...
`creationtime` timestamp NULL default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

An explanation as to why they cannot have one column default to CURRENT_TIMESTAMP on creation and another column be the CURRENT_TIMESTAMP on update would be nice. I find myself in creation of many tables where I need this functionality on two separate columns.

Posted by mari a on March 12 2008 10:36pm[Delete] [Edit]

"TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp."

What I do, is declare my created_at column as NOT NULL, then assign the column as NULL when I do the insert.. which populates it with the current timestamp.

Then on updates, the second timestamp column with the ON UPDATE...etc updates on it's own. However, the update code must not alter the created_at column at all (obviously).

created_at timestamp NOT NULL default '0000-00-00 00:00:00'
updated_at timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

INSERT INTO table(created_at,updated_at) VALUES (NULL,NULL);

populates BOTH columns with same timestamp for creation.



Posted by sanjuro tsubaki on March 18 2008 2:15pm[Delete] [Edit]

If the value(s) you are trying to update are identical, MySQL will not auto-update the timestamp alone. You will have to use NOW() instead.

Posted by Ashley Raiteri on July 5 2008 7:54pm[Delete] [Edit]

These last two posts are the only mechanism that worked for us.
In any system, hand coded, phpmyadmin, mysql query browser or otherwise :

`created` timestamp NOT NULL default '0000-00-00 00:00:00',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update NOW(),

although, phpmyadmin exports this out without the NOW, replacing it with CURRENT_TIMESTAMP instead.

Posted by Chris Heath on July 29 2008 3:32am[Delete] [Edit]

Note that using CURRENT_TIMESTAMP explicitly in an INSERT or UPDATE does not always insert the current timestamp. The only correct way is to insert NULL or to omit the timestamp column entirely.

With an explicit CURRENT_TIMESTAMP, MySQL converts the current timestamp to YYYY-MM-DD HH-MM-SS format and then back to a timestamp, which is a lossy conversion if you are in a timezone with daylight savings.

Example to demonstrate: (assumes US Eastern Time)
\! date 110400592007.59
Sun Nov 4 00:59:59 EDT 2007
\! sleep 2
CREATE TABLE test (id int, t timestamp);
INSERT INTO test VALUES (1, NULL), (2, CURRENT_TIMESTAMP);
SELECT test.*, UNIX_TIMESTAMP(t) FROM test;
+------+---------------------+-------------------+
| id | t | UNIX_TIMESTAMP(t) |
+------+---------------------+-------------------+
| 1 | 2007-11-04 01:00:01 | 1194152401 |
| 2 | 2007-11-04 01:00:01 | 1194156001 |
+------+---------------------+-------------------+

The second row is wrong: the unix timestamp is 1 hour (3600 seconds) later, a result of a lossy round-trip conversion.

Posted by michael splendid on August 27 2008 5:40pm[Delete] [Edit]

using ALTER to add a TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP attribute, will not add the current TIMSTAMP to that column (as expected)
Mike Trader

Posted by Bogdan Gusiev on May 11 2009 4:42pm[Delete] [Edit]

Here is a solution how to make both update and create timestamps in mysql in one table:
http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

Posted by Stefan Zielinski on May 11 2009 11:07pm[Delete] [Edit]

The example in Bogdan Gusiev's link is essentially the same as from the 5th post above ("Posted by mari a on March 12 2008 10:36pm"), and while it does work, it can also be achieved by declaring the following table:

CREATE TABLE test.table (
`row_inserted` TIMESTAMP NULL,
`row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

...and then specifying CURRENT_TIMESTAMP explicitly in the insert statement:

INSERT INTO test.table (row_inserted) VALUES (CURRENT_TIMESTAMP)

...all of these solutions require that the code doing the insert explicitly set a value for the created / inserted timestamp column - whether it is NULL or CURRENT_TIMESTAMP - there doesn't seem to be a way for MySQL to handle this implicitly.

Ideally, these columns would never need to be explicitly referenced by any insert or update query, and instead be set automatically, with a declaration like this:

CREATE TABLE test.table (
`row_inserted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

...and I'm not sure why MySQL chose not to support this - it doesn't seem like there should be any real technical limitation that prevents it.

Posted by Rob Desbois on July 29 2009 8:24am[Delete] [Edit]

We recently modified a DATETIME column to be of type TIMESTAMP as we wanted to take advantage of the timezone-related functionality, but found there's no way to create a TIMESTAMP column with no default and no 'on update' value.

The 2-step process to achieve this is as follows:
CREATE TABLE foo (ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE foo ALTER ts DROP DEFAULT;

Posted by Joseph Piché on August 4 2009 2:15pm[Delete] [Edit]

It may help someone that depending on table structure and the amount of data in the table, if you need to ORDER BY a TIMESTAMP column, it may speed up the query by using ORDER BY UNIX_TIMESTAMP(`field`).

Add your own comment.