MySQL Reference Manual :: 11.3.1.2 TIMESTAMP Properties as of MySQL 4.1

In MySQL 4.1 and up, the properties of the TIMESTAMP column type change in the ways described in this section.

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

Beginning with MySQL 4.1.1, the MySQL server can be run in MAXDB mode. When the server runs in this mode, TIMESTAMP is identical with DATETIME. That is, if the server is running in MAXDB mode 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;

As of MySQL 5.0.2, MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. (The exception is the special value '0000-00-00 00:00:00'.)

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:

The following discussion describes the revised syntax and behavior. Note that this information applies only to TIMESTAMP columns for tables not created with MAXDB mode enabled. As noted earlier in this section, MAXDB mode causes columns to be created as DATETIME columns.

The following items summarize the pre-4.1.2 properties for TIMESTAMP initialization and updating:

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.

As of 4.1.2, you have more flexibility in deciding which TIMESTAMP column automatically is initialized and updated to the current timestamp. The rules are as follows:

If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.

DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.

Any single TIMESTAMP column in a table can be set to be the one that is initialized to the current timestamp and/or updated automatically.

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

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 get auto-update without also having the column auto-initialized.)

Any of CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or NOW() can be used in the DEFAULT and ON UPDATE clauses. They all have the same effect.

The order of the two attributes does not matter. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other.

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);

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 you cannot omit both of the DEFAULT and ON UPDATE clauses. If you do that, no automatic initialization or updating occurs.

Example. These statements are equivalent:

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

Beginning with MySQL 4.1.3, you can set the current time zone on a per-connection basis, as described in 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 the same, you get back the same value you store. If you store a TIMESTAMP value, then change the time zone and retrieve the value, it is different than the value you stored. This occurs because the same time zone is not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

Beginning with MySQL 4.1.6, you can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example:

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

Before MySQL 4.1.6 (and even as of 4.1.6 if the NULL attribute is not specified), setting the column to NULL sets it to the current timestamp. Note that a TIMESTAMP column which allows NULL values will not take on the current timestamp unless either its default value is defined as CURRENT_TIMESTAMP, or either NOW() or CURRENT_TIMESTAMP is inserted into the column. In other words, a TIMESTAMP column defined as NULL will auto-update only if it defined using a definition such as the following:

CREATE TABLE t (ts NULL DEFAULT CURRENT_TIMESTAMP);

Otherwise - that is, if the TIMESTAMP column is defined using NULL but not using DEFAULT TIMESTAMP, as shown here:

CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts 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);