In older versions of MySQL (prior to 4.1), the properties of
the TIMESTAMP data type
differed significantly in several ways from what is
described in this section (see the MySQL 3.23,
4.0, 4.1 Reference Manual for details); these
include syntax extensions which are deprecated in MySQL 5.1,
and no longer supported in MySQL 5.5. This has implications
for performing a dump and restore or replicating between
MySQL Server versions. If you are using columns that are
defined using the old
TIMESTAMP(N) syntax, see
Section 2.19.1.2, “Upgrading from MySQL 4.1 to 5.0”, prior to
upgrading to MySQL 5.1 or later.
The TIMESTAMP data type offers
automatic initialization and updating to the current date and
time (that is, the current timestamp). You can choose whether
to use these properties and which column should have them:
One TIMESTAMP column in a
table can have the current timestamp as the default value
for initializing the column, as 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.
If the column is auto-initialized, it is set to the current timestamp for inserted rows that specify no value for the column.
If the column is auto-updated, it is automatically updated
to the current timestamp when the value of any other
column in the row is changed from its current value. The
column remains unchanged if all other columns are set to
their current values. To prevent the column from updating
when other columns change, explicitly set it to its
current value. To update the column even when other
columns do not change, explicitly set it to the value it
should have (for example, set it to
CURRENT_TIMESTAMP).
In addition, you can initialize or update any
TIMESTAMP column to the current
date and time by assigning it a NULL value,
unless it has been defined with the NULL
attribute to permit NULL values.
To specify automatic properties, use the DEFAULT
CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP clauses. The order of the clauses
does not matter. If both are present in a column definition,
either can occur first. Any of the synonyms for
CURRENT_TIMESTAMP have the same
meaning as CURRENT_TIMESTAMP.
These are CURRENT_TIMESTAMP(),
NOW(),
LOCALTIME,
LOCALTIME(),
LOCALTIMESTAMP, and
LOCALTIMESTAMP().
Use of DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP is specific to
TIMESTAMP. The
DEFAULT clause also can be used to specify
a constant (nonautomatic) default value; for example,
DEFAULT 0 or DEFAULT '2000-01-01
00:00:00'.
The following examples that use DEFAULT 0
do not work if the
NO_ZERO_DATE SQL mode is
enabled because that mode causes “zero” date
values (specified, for example, as 0
'0000-00-00 00:00:00') to be rejected. Be
aware that the TRADITIONAL
SQL mode includes
NO_ZERO_DATE.
The following rules describe the possibilities for defining
the first TIMESTAMP column in a
table with the current timestamp for both the default and
auto-update values, for one but not the other, or for neither:
With both DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP, the column
has the current timestamp for its default value and is
automatically updated to the current timestamp.
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
With neither DEFAULT CURRENT_TIMESTAMP
nor ON UPDATE CURRENT_TIMESTAMP, it is
the same as specifying both DEFAULT
CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP.
CREATE TABLE t1 ( ts TIMESTAMP );
With a DEFAULT clause but no
ON UPDATE CURRENT_TIMESTAMP clause, the
column has the given default value and is not
automatically updated to the current timestamp.
The default depends on whether the
DEFAULT clause specifies
CURRENT_TIMESTAMP or a constant value.
With CURRENT_TIMESTAMP, the default is
the current timestamp.
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
With a constant, the default is the given value. In this case, the column has no automatic properties at all.
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 );
With an ON UPDATE CURRENT_TIMESTAMP
clause and a constant DEFAULT clause,
the column is automatically updated to the current
timestamp and has the given constant default value.
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP );
With an ON UPDATE CURRENT_TIMESTAMP
clause but no DEFAULT clause, the
column is automatically updated to the current timestamp.
The default is 0 unless the column is defined with the
NULL attribute, in which case the
default is NULL.
CREATE TABLE t1 ( ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- default 0 ); CREATE TABLE t2 ( ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL );
It need not be the first
TIMESTAMP column in a table
that is automatically initialized or updated to the current
timestamp. However, to specify automatic initialization or
updating for a different
TIMESTAMP column, you must
suppress the automatic properties for the first one. Then, for
the other TIMESTAMP column, the
rules for the DEFAULT and ON
UPDATE clauses are the same as for the first
TIMESTAMP column, except that
if you omit both clauses, no automatic initialization or
updating occurs.
To suppress automatic properties for the first
TIMESTAMP column, do either of
the following:
Define the column with a DEFAULT clause
that specifies a constant default value.
Specify the NULL attribute. This also
causes the column to permit NULL
values, which means that you cannot assign the current
timestamp by setting the column to
NULL. Assigning NULL
sets the column to NULL.
Consider these table definitions:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);The tables have these properties:
In each table definition, the first
TIMESTAMP column has no
automatic initialization or updating.
The tables differ in how the ts1 column
handles NULL values. For
t1, ts1 is
NOT NULL and assigning it a value of
NULL sets it to the current timestamp.
For t2 and t3,
ts1 permits NULL and
assigning it a value of NULL sets it to
NULL.
t2 and t3 differ in
the default value for ts1. For
t2, ts1 is defined
to permit NULL, so the default is also
NULL in the absence of an explicit
DEFAULT clause. For
t3, ts1 permits
NULL but has an explicit default of 0.
TIMESTAMP Initialization and the NULL Attribute
By default, TIMESTAMP columns
are NOT NULL, cannot contain
NULL values, and assigning
NULL assigns the current timestamp. To
permit a TIMESTAMP column to
contain NULL, explicitly declare 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 permits
NULL values, assigning
NULL sets it to NULL,
not to the current timestamp.
The following table contains several
TIMESTAMP columns that permit
NULL values:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
A TIMESTAMP column that permits
NULL values does not
take on the current timestamp at insert time except under one
of the following conditions:
Its default value is defined as
CURRENT_TIMESTAMP and no
value is specified for the column
CURRENT_TIMESTAMP or any of
its synonyms such as NOW()
is explicitly inserted into the column
In other words, a TIMESTAMP
column defined to permit NULL values
auto-initializes only if its definition includes
DEFAULT CURRENT_TIMESTAMP:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
If the TIMESTAMP column permits
NULL values but its definition does not
include DEFAULT CURRENT_TIMESTAMP, you must
explicitly insert a value corresponding to the current date
and time. Suppose that tables t1 and
t2 have these definitions:
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'); CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);
To set the TIMESTAMP column in
either table to the current timestamp at insert time,
explicitly assign it that value. For example:
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);