MySQL supports a TIMESTAMP
data type that stores date-and-time values. Earlier sections covered
the range of values for TIMESTAMP
(Choosing a Temporal Data Type) and the conversion of
TIMESTAMP
values to and from UTC when they are stored and retrieved (Setting the Client Time Zone). This section focuses
on how TIMESTAMP
columns enable you
to track row creation and update times automatically:
One TIMESTAMP
column in a
table can be treated as special in either or both of
the following ways:
The column is automatically initialized to the current
date and time when new rows are created. This means you need
not specify its value at all in an INSERT
statement; MySQL initializes
it automatically to the row’s creation time. (This also occurs
if you set the column to NULL
.)
The column is automatically updated to the current date
and time when you change any other column in the row from its
current value. The update happens only if you actually
change a column value; setting a column
to its current value doesn’t update the TIMESTAMP
.
This auto-update property sometimes surprises people who
don’t realize that changing another column also updates the
TIMESTAMP
column. This will
never surprise you, of course, because
you’re aware of it!
There can be multiple TIMESTAMP
columns in a table, but only
one of them can have the special properties just described. Other
TIMESTAMP
columns have a
default value of zero, not the current date and time. Also, their
value does not change automatically when you modify other columns;
to update them, you must change them yourself.
A TIMESTAMP
column can be
updated to the current date and time at any time by setting it to
NULL
, unless it has
specifically been defined to allow NULL
values. This is true for any
TIMESTAMP
column, not just the
first one.
The special properties that relate to row creation and
modification make the TIMESTAMP
data type particularly suited
for certain kinds of problems, such as automatically recording the
times at which table rows are inserted or updated. The following
discussion shows how to take advantage of these properties.
The syntax for defining TIMESTAMP
columns is covered in full in the
MySQL Reference Manual. Here we cover only some
simple cases. By default, if you specify no special handling for the
first TIMESTAMP
column in a table,
it is equivalent to specifying that it should have both the
auto-initialize and auto-update properties explicitly. You can see
this as follows, where the SHOW
CREATE
TABLE
statement displays the full TIMESTAMP
definition that results from the
CREATE
TABLE
statement:
mysql>CREATE TABLE t (ts TIMESTAMP);
mysql>SHOW CREATE TABLE tG
*************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1
If you explicitly specify only the DEFAULT
or ON
UPDATE
attribute for the TIMESTAMP
column, it has only that attribute
and not the other one.
The NOT
NULL
shown in the column definition might
seem a curious thing, given that you can insert
NULL
into the column, and no error
occurs. What this means is that, although you can specify NULL
as the value to be inserted, you cannot
store
NULL
because MySQL stores the current date and time instead.
To create a table in which each row contains a value that
indicates when the row was created or most recently updated, include a
TIMESTAMP
column. MySQL will set
the column to the current date and time when you create a new row, and
update the column whenever you update the value of another column in
the row. Suppose that you create a table tsdemo1
with a TIMESTAMP
as one of its columns:
CREATE TABLE tsdemo1 ( ts TIMESTAMP, val INT );
In this case, the ts
column
has both the auto-initialize and auto-update properties, for reasons
just discussed. Insert a couple of rows into the table, and then
select its contents. (Issue the INSERT
statements a few seconds apart so
that the timestamps differ.) The first INSERT
statement shows that you can set
ts
to the current date and time by
omitting it from the INSERT
statement entirely; the second shows that you can do so by setting
ts
explicitly to NULL
:
mysql>INSERT INTO tsdemo1 (val) VALUES(5);
mysql>INSERT INTO tsdemo1 (ts,val) VALUES(NULL,10);
mysql>SELECT * FROM tsdemo1;
+---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:21:26 | 5 | | 2006-06-03 08:21:31 | 10 | +---------------------+------+
Now issue a statement that changes one row’s val
column, and check its effect on the
table’s contents:
mysql>UPDATE tsdemo1 SET val = 6 WHERE val = 5;
mysql>SELECT * FROM tsdemo1;
+---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:21:52 | 6 | | 2006-06-03 08:21:31 | 10 | +---------------------+------+
The result shows that the TIMESTAMP
column of the modified row was
updated.
If you modify multiple rows, the TIMESTAMP
value in each of them is
updated:
mysql>UPDATE tsdemo1 SET val = val + 1;
mysql>SELECT * FROM tsdemo1;
+---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:22:00 | 7 | | 2006-06-03 08:22:00 | 11 | +---------------------+------+
An UPDATE
statement that
doesn’t actually change the value in the val
column doesn’t update the TIMESTAMP
value. To see this, set every
row’s val
column to its current
value, and then review the contents of the table:
mysql>UPDATE tsdemo1 SET val = val;
mysql>SELECT * FROM tsdemo1;
+---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:22:00 | 7 | | 2006-06-03 08:22:00 | 11 | +---------------------+------+
If you want the TIMESTAMP
column to be set initially to the time at which a row is created, but
to remain constant thereafter, define it to auto-initialize but not
auto-update. To do this, the TIMESTAMP
definition can be given as shown
in the following table:
CREATE TABLE tsdemo2 ( t_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, val INT );
Create the table, and then insert records into it with the
TIMESTAMP
column not specified (or
specified as NULL
) to initialize it
to the current date and time:
mysql>INSERT INTO tsdemo2 (val) VALUES(5);
mysql>INSERT INTO tsdemo2 (t_create,val) VALUES(NULL,10);
mysql>SELECT * FROM tsdemo2;
+---------------------+------+ | t_create | val | +---------------------+------+ | 2006-06-03 08:26:00 | 5 | | 2006-06-03 08:26:05 | 10 | +---------------------+------+
After inserting the records, change the val
column, and then verify that the update
leaves the t_create
column
unchanged (that is, set to the record-creation time):
mysql>UPDATE tsdemo2 SET val = val + 1;
mysql>SELECT * FROM tsdemo2;
+---------------------+------+ | t_create | val | +---------------------+------+ | 2006-06-03 08:26:00 | 6 | | 2006-06-03 08:26:05 | 11 | +---------------------+------+
If you want to simulate the TIMESTAMP
auto-initialization and
auto-update properties for other temporal types, you can use triggers
(Simulating TIMESTAMP Properties for Other Date and Time Types
).
3.21.247.16