5.32 Using TIMESTAMP Values
5.32.1 Problem
You want a
record's creation time or last modification time to
be automatically recorded.
5.32.2 Solution
The TIMESTAMP column type can be used for this.
However, it has properties that sometimes surprise people, so read
this section to make sure you know what you'll be
getting. Then read the next few sections for some applications of
TIMESTAMP columns.
5.32.3 Discussion
MySQL supports a TIMESTAMP column type
that in many ways can be treated the same way as the
DATETIME type. However, the
TIMESTAMP type has some special properties:
The first TIMESTAMP column in a table is special
at record-creation time: its default value is the current date and
time. This means you need not specify its value at all in an
INSERT statement if you want the column set to the
record's creation time; MySQL will initialize it
automatically. This also occurs if you set the column to
NULL when creating the record.
The first TIMESTAMP is also special whenever any
columns in a row are changed from their current values. MySQL
automatically updates its value to the date and time at which the
change was made. Note that the update happens only if you actually
change a column value. Setting a column to its
current value doesn't update the
TIMESTAMP.
Other TIMESTAMP columns in a table are not special
in the same way as the first one. Their default value is 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 set to the current date
and time at any time by setting it to NULL. This
is true for any TIMESTAMP column, not just the
first one.
The TIMESTAMP properties that relate to record
creation and modification make this column type particularly suited
for certain kinds of problems, such as automatically recording the
times at which table rows are inserted or updated. On the other hand,
there are other properties that can be somewhat limiting:
TIMESTAMP
values are represented in CCYYMMDDhhmmss
format, which isn't especially intuitive or easy to
read, and often needs reformatting for display.
The range for TIMESTAMP values starts at the
beginning of the year 1970 and extends to about 2037. If you need a
larger range, you need to use DATETIME values.
The following sections show how to take advantage of the
TIMESTAMP type's special
properties.
|
