Tech Erudition

CREATE TABLE record (createdon TIMESTAMP, modifiedon TIMESTAMP);
INSERT INTO  record (createdon, modifiedon) VALUES ('2010-10-01 11:01:02', '2010-10-01 11:01:02');

SELECT * FROM record;
+---------------------+---------------------+
| createdon           | modifiedon          |
+---------------------+---------------------+
| 2010-10-01 11:01:02 | 2010-10-01 11:01:02 |
+---------------------+---------------------+

ALTER TABLE record ADD COLUMN activeon TIMESTAMP;
UPDATE record SET activeon=modifiedon;

SELECT * FROM record;
+---------------------+---------------------+---------------------+
| createdon           | modifiedon          | activeon            |
+---------------------+---------------------+---------------------+
| 2012-10-11 01:47:41 | 2010-10-01 11:01:02 | 2010-10-01 11:01:02 |
+---------------------+---------------------+---------------------+

Why did createdon value change???

SHOW CREATE TABLE record;

CREATE TABLE `record` (
  `createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modifiedon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activeon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

MySQL applies the default rule when timestamp data-type is specified during column creation.
The 1st column of table (without explicit rule) gets a rule for update CURRENT_TIMESTAMP

Which would mean, if during update of record a value is not provided for the column the
value will get updated automatically to current timestamp.

Which was the reason why createdon got changed when activeon was updated!

How to tackle this behavior?

If application is controlling the data value on timestamp columns, when creating the timestamp
column we should ensure to mention the default rule.

CREATE TABLE record (createdon TIMESTAMP NULL DEFAULT NULL, modifiedon TIMESTAMP NULL DEFAULT NULL);
ALTER TABLE record ADD COLUMN activeon TIMESTAMP NULL DEFAULT NULL;

This helps in avoiding surprises!