On Update Current Timestamp

[andrewteg]

I am creating tables and want to have something like this:

Code:

ALTER TABLE section ADD sectUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

According to this FAQ I can do that by selecting CURRENT_TIMESTAMP and NOT NULL which I've done. However it does not work.

To make it worse, if I run the code above after the fact, and then alter the table and change anything about the sectUpdated column, it reverts to just a TIMESTAMP and not ON UPDATE CURRENT_TIMESTAMP anymore.

How can I fix this? Here is the entire table I tried to create as a test:

Code:

CREATE TABLE `section` (
`sectionID` INT(11) NOT NULL AUTO_INCREMENT,
`sectionTitle` VARCHAR(50) NOT NULL,
`sectionURL` VARCHAR(50) DEFAULT NULL,
`sectionCreated` DATETIME DEFAULT NULL,
`sectionUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`sectionID`)
) ENGINE=MYISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

[peterlaursen]

“According to this FAQ I can do that by selecting CURRENT_TIMESTAMP and NOT NULL”

NOPE! There is no option to 'select CURRENT_TIMESTAMP'. Do you mean you enter this in default coloumn?

Please read the FAQ again! You shall ONLY check 'NOT NULL'. Do not enter any default at all and the server will make it like this (because that is default in MySQL from 4.1 for the first NOT NULL timestamp of a table)

Code:

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

[attachment=1200:create.jpg]

[attachment=1201:result.jpg]

[andrewteg]

Thanks! You're absolutely right and apparently I need to learn how to read 😮

It is working exactly as the FAQ claims. Thanks again.

[peterlaursen]

No problem .. I give training courses in reading at 400$ per hour!

Jokes apart: this is server behaviour: the first NOT NULL timestamp of a table will become NOT NULL CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP unless otherwise specified. As the FAQ tells we *could* add an ON UPDATE column to CREATE/ALTER TABLE grid. But as timestamps is the only data type where it applies currently, we have decided not to do now. Should the ON UPDATE option some day be added to more data types or should the MySQL timestamp implementation change we will do when we (or users) find it required.

[Author]

Posts