Using TIMESTAMP Values
Problem
You want a record’s creation time or last modification time to be automatically recorded.
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.
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 anINSERT
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 toNULL
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 theTIMESTAMP
.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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.