11.3.1.1. TIMESTAMP Properties as of MySQL 4.1
Note: In older versions of
MySQL (prior to 4.1), the properties of the
TIMESTAMP data type differed significantly
in many ways from what is described in this section. If you
need to convert older TIMESTAMP data to
work with MySQL 5.1, be sure to see the
MySQL 3.23, 4.0, 4.1 Reference Manual for details.
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.
The MySQL server can be also 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;
Note that the information in the following discussion applies
to TIMESTAMP columns only for tables not
created with MAXDB mode enabled, because
such columns are created as DATETIME
columns.
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 sole exception to this rule is the special value
'0000-00-00 00:00:00'.
You have considerable flexibility in determining 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.
You can specify which TIMESTAMP column
to automatically initialize or update to the current date
and time. This need not be the first
TIMESTAMP column.
The following rules govern initialization and updating of
TIMESTAMP columns:
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 used as the one that is initialized to the current
timestamp or updated automatically.
-
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. If the column has an
ON UPDATE CURRENT_TIMESTAMP clause,
it is automatically updated, otherwise not.
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.)
-
Any of CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP(), or
NOW() can be used in the
DEFAULT and ON
UPDATE clauses. They all mean “the current
timestamp.”
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);
-
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. 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);
You can set the current time zone on a per-connection basis,
as described in Section 5.10.8, “MySQL Server Time Zone Support”.
TIMESTAMP values are stored in UTC, being
converted from the current time zone for storage, and
converted back to the current time zone upon 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
than 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.
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
);
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 except
under one of the following conditions:
In other words, a TIMESTAMP column defined
as NULL will auto-update 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 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);
