Automatically set timestamps for "create" and "modified"?

Question

I have currently following SQL table:

CREATE TABLE IF NOT EXISTS `xyz` (
 `id` bigint(20) NOT NULL,
 `guid` char(36) NOT NULL,
 `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `ts_modified` timestamp NULL DEFAULT NULL,
 ................
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT AUTO_INCREMENT=1 ;

ts_created is the timestamp when the entry was created. The default value is CURRENT_TIMESTAMP.

ts_modified is the timestamp when the entry was updated.

In phpMyAdmin, I could choose ON UPDATE CURRENT_TIMESTAMP for ts_modified. This would be exactly what I want.

But the error message is following:

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

The error message is clear to me, but it is not clear to me

1) why this constraint is existing, since the "default" value (on create) and the "on update" value are two different things.

2) Is there anything I could do, or do I have to add ts_modified = NOW() at every UPDATE statement?

Answer 1

From here:

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

Changes in MySQL 5.6.5 (2012年04月10日, Milestone 8)

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

What can you do?

You can add a BEFORE UPDATE trigger and set the ts_modified to NOW() like this:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.xyz_BEFORE_UPDATE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`xyz_BEFORE_UPDATE` BEFORE UPDATE ON `xyz` FOR EACH ROW
BEGIN
SET NEW.ts_modified=NOW();
END
$$
DELIMITER ;

• Daniel Marschall: Thank you for this hint, and the trigger definition. It is the first time I am using triggers in MySQL ;)

Answer 2

You could easily change the column type to datetime2. Your default values would continue to work.

Timestamp is a special column type and is used to generate an absolutely unique number within the entire database. If you need a date/time they recommend datetime2.

link link

• Daniel Marschall: Sorry, I forgot to mention I am using MySQL (although ENGINE=InnoDB suggests it). datetime2 is not existing in MySQL. And datetime is not necessarily UTC, which may lead to problems when it is not written&interpreted carefully.
• Brodie Brickey: try UTC_Timestamp() or UTC_Date()