5.34 Recording a Row's Creation Time
5.34.1 Problem
You want to record the
time when a record was created, which TIMESTAMP
will do, but you want that time not to change when the record is
changed, and a TIMESTAMP cannot hold its value.
5.34.2 Solution
Actually, it can; you just need to include a second
TIMESTAMP column, which has different properties
than the first.
5.34.3 Discussion
If you want a column to be set initially to the time at which a
record is created, but remain constant thereafter, a single
TIMESTAMP is not the solution, because it will be
updated whenever other columns in the record are updated. Instead,
use two TIMESTAMP columns and take advantage of
the fact that the second one won't have the same
special properties of the first. Both columns can be set to the
current date and time when the record is created. Thereafter,
whenever you modify other columns in the record, the first
TIMESTAMP column will be updated automatically to
reflect the time of the change, but the second remains set to the
record creation time. You can see how this works using the following
table:
CREATE TABLE tsdemo2
(
t_update TIMESTAMP, # record last-modification time
t_create TIMESTAMP, # record creation time
val INT
);
Create the table, then insert into it as follows a record for which
both TIMESTAMP columns are set to
NULL, to initialize them to the current date and
time:
mysql> INSERT INTO tsdemo2 (t_update,t_create,val) VALUES(NULL,NULL,5);
mysql> SELECT * FROM tsdemo2;
+----------------+----------------+------+
| t_update | t_create | val |
+----------------+----------------+------+
| 20020715120003 | 20020715120003 | 5 |
+----------------+----------------+------+
After inserting the record, change the val column,
then verify that the update modifies the t_update
column and leaves the t_create column set to the
record-creation time:
mysql> UPDATE tsdemo2 SET val = val + 1;
mysql> SELECT * FROM tsdemo2;
+----------------+----------------+------+
| t_update | t_create | val |
+----------------+----------------+------+
| 20020715120012 | 20020715120003 | 6 |
+----------------+----------------+------+
As with the tsdemo1 table, updates to
tsdemo2 records that don't
actually modify a column cause no change to
TIMESTAMP values:
mysql> UPDATE tsdemo2 SET val = val + 0;
mysql> SELECT * FROM tsdemo2;
+----------------+----------------+------+
| t_update | t_create | val |
+----------------+----------------+------+
| 20020715120012 | 20020715120003 | 6 |
+----------------+----------------+------+
An alternative strategy is to use DATETIME columns
for t_create and t_update. When
creating a record, set them both to NOW( )
explicitly. When modifying a record, update
t_update to NOW( ) and leave
t_create alone.
|
