Hello all -

I would like to have a table with a column that shows me the timestamp
of when the row was inserted, and another column of when the row was
last updated. Ideally, these values would fill in automatically on
INSERTs and UPDATEs.

I was looking at the MySQL timestamp column properties for 5.0, and it
looks like what I want is impossible. It seems that only one timestamp
column in a table can have an automatically set timestamp.

CREATE TABLE `guestrak`.`timestamp_test` (
`id` INT UNSIGNED NOT NULL ,
`modification` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,
`creation` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

MySQL said: Documentation
#1293 - Incorrect table definition; there can be only one TIMESTAMP
column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

[output from phpMyAdmin]

So, that means that I would have to set the value for the creation
timestamp column manually, and then use an automated column for the
modification timestamp.

Is that correct?

lawpoop@gmail.com wrote:
> Hello all -
>
> I would like to have a table with a column that shows me the timestamp
> of when the row was inserted, and another column of when the row was
> last updated. Ideally, these values would fill in automatically on
> INSERTs and UPDATEs.
>
> I was looking at the MySQL timestamp column properties for 5.0, and it
> looks like what I want is impossible. It seems that only one timestamp
> column in a table can have an automatically set timestamp.
>
> CREATE TABLE `guestrak`.`timestamp_test` (
> `id` INT UNSIGNED NOT NULL ,
> `modification` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,
> `creation` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
> PRIMARY KEY ( `id` )
> ) ENGINE = InnoDB
>
> MySQL said: Documentation
> #1293 - Incorrect table definition; there can be only one TIMESTAMP
> column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
>
> [output from phpMyAdmin]
>
> So, that means that I would have to set the value for the creation
> timestamp column manually, and then use an automated column for the
> modification timestamp.
>
> Is that correct?

Sounds like it to me

On Tue, 27 Nov 2007 10:01:13 -0800 (PST), lawpoop@gmail.com wrote:
> So, that means that I would have to set the value for the creation
> timestamp column manually, and then use an automated column for the
> modification timestamp.
>
> Is that correct?

Yup. NOW() is your friend for that insert.

--
99. Any data file of crucial importance will be padded to 1.45Mb in size.
--Peter Anspach's list of things to do as an Evil Overlord