Recording a Rows Creation Time

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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net