List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 9 1999 7:44pm
Subject:Re: Problem with mutiple columns of type TIMESTAMP
View as plain text  
At 3:23 PM -0400 7/9/99, Denny Chambers wrote:
>>Description:
>        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.
>
>>How-To-Repeat:
>        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
>system time.


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
   automatically updated):

      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/
Thread
Problem with mutiple columns of type TIMESTAMPDenny Chambers9 Jul
  • Re: Problem with mutiple columns of type TIMESTAMPBenjamin Pflugmann9 Jul
  • Re: Problem with mutiple columns of type TIMESTAMPPaul DuBois9 Jul
  • Re: Problem with mutiple columns of type TIMESTAMPDaniel Koch10 Jul
    • Re: Problem with mutiple columns of type TIMESTAMPPaul DuBois10 Jul
  • Re: Problem with mutiple columns of type TIMESTAMPFred Read10 Jul
    • Re: Problem with mutiple columns of type TIMESTAMPLuke10 Jul
RE: Problem with mutiple columns of type TIMESTAMPDarren L Sweeney9 Jul
  • Re: Problem with mutiple columns of type TIMESTAMPBenjamin Pflugmann9 Jul