Bug #65471 Need to have two timestamps in one table, one filled on create, second on update

• Submitted: 31 May 2012 12:41
• Modified: 31 May 2012 19:34
• Reporter: Jakub Pawlinski
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S5 (Performance)
• OS: Any
• CPU Architecture: Any
• Tags: CURRENT_TIMESTAMP on update timestamp two fields

[31 May 2012 12:41] Jakub Pawlinski

Description:
Why both (on_insert and on_update) have to be in one (and only) field?

I would like to have structure like:
* id         - int(10) unsigned
* name       - varchar(50)
* inserted   - timestamp CURRENT_TIMESTAMP
* updated    - timestamp on update CURRENT_TIMESTAMP

How to repeat:
CREATE TABLE `dates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t1` date DEFAULT NULL,
  `t2` date DEFAULT NULL,
  `t3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `t4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1;

[31 May 2012 14:02] Peter Laursen

You can do as you want with MySQL 5.6.5.

Refer for instance:
http://mysqlblog.fivefarmers.com/2012/05/29/overlooked-mysql-5-6-new-features-timestamp-an...

+ MySQL 5.6 documentation:
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Peter
(not a MySQL person)

[31 May 2012 14:56] Valeriy Kravchuk

In older versions you can just create trigger for update of each row.

[31 May 2012 19:34] Jakub Pawlinski

Ok, thanks Peter. I will migrate asap.

Cheers