Timestamp For "created" And "edited"

[Still Learning]

I've read through the mysql timestamp manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html) a couple of times but can't seem to understand their explanation (having tried unsuccessfully in sqlyog schema designer to add auto increment and/or the ON UPDATE clauses etc).

Succinctly put, I have a TABLE with columns CREATED and EDITED and want

CREATED to have a CURRENT_TIMESTAMP default value

and

EDITED to have an ON UPDATE CURRENT_TIMESTAMP default value

what values to put where?

[Mahesh]

Still Learning wrote on Nov 20 2007, 05:06 AM:

I've read through the mysql timestamp manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html) a couple of times but can't seem to understand their explanation (having tried unsuccessfully in sqlyog schema designer to add auto increment and/or the ON UPDATE clauses etc).

Succinctly put, I have a TABLE with columns CREATED and EDITED and want

CREATED to have a CURRENT_TIMESTAMP default value

and

EDITED to have an ON UPDATE CURRENT_TIMESTAMP default value

what values to put where?

HI,

You can't make two columns having Current_timestamp and on update current_timestamp as default value.

MYsql doesn't allow this and will throw an error

Error Code : 1293

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

try to execute the below sql statement will throw an error

CREATE TABLE t (

ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP);

Hope that i understood your problem

[peterlaursen]

it is a limitation with MySQL itself that only one TIMESTAMP column can use CURRENT_TIMESTAMP in definition.

[Still Learning]

I assume so, in that when I set two columns to Timestamp datatype, one defaults (automatically) to CURRENT_TIMESTAMP and the other to:

0000-00-00 00:00:00

Yet, — and I know from the manual — you can set two columns to record a date/time in the same table. So, how do I set one to record the time when the row was created and one to record the time the row was (last) updated?

[peterlaursen]

you cannot use CURRENT_TIMESTAMP more than once in table definition.

Try yourself:

create table `TableName1` (

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT ,

`ts1` timestamp DEFAULT CURRENT_TIMESTAMP,

`ts2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`))

returns:

Error Code : 1293

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

You will have to write current time to the second timestamp from the application using this table like

UPDATE TABLE … SET `ts2` = now(); — 'now() is a handy synonym for CURRENT_TIMESTAMP only!

.. you cannot solve this in the table definition! You may post a feature request to bugs.mysql.com, but I bet they heard it before!

[Author]

Posts