At 3:23 PM -0400 7/9/99, Denny Chambers wrote:
> When a table has multiple columns that are of type TIMESTAMP,
>the first TIMESTAMP column seems
> to get updated with the current system time if any of the
>subsuquent TIMESTAMP columns are updated.
> Create a table with two columns of type TIMESTAMP. Insert two
>TIMESTAMP values into the columns.
> Now update the second column. The first column will pick up the
That's what's supposed to happen. It's how TIMESTAMP is defined to work.
Here's some stuff out of a rough draft that applies to this issue:
MySQL does not have a column type that can be set to the current date
and time when the record is created and that remains immutable
thereafter. If that is what you want to achieve, you can do it two ways:
* First, you can use a TIMESTAMP column. When a record is first created,
set the column to NULL (to initialize the column to the current date
and time). Whenever you update the record, explicitly set the column
to its current value (to prevent the column's value from being
mysql> INSERT INTO tbl_name (ts_col, ...) VALUES(NULL, ...);
mysql> UPDATE tbl_name SET ts_col=ts_col WHERE ... ;
* Second, you can use a DATETIME column. When you create the record,
initialize the column to NOW(). Whenever you update the record, leave
the column alone:
mysql> INSERT INTO tbl_name (dt_col, ...) VALUES(NOW(), ...);
mysql> UPDATE tbl_name SET /* anything BUT dt_col here / WHERE ... ;
If you want to maintain both a time-created value and a last-modified
value, do so as follows:
* Use one TIMESTAMP for the time-modified value, and a second TIMESTAMP
for the time-created value.
* Make sure the time-modified column is the first TIMESTAMP, so that
it's set when the record is created or changed.
* Make the time-created column the second TIMESTAMP, and initialize it
to NOW() when you create new records. That way its value will reflect
the record creation time and will not change after that.
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/