MySQL Workbench: how do I set up "ON UPDATE" and CURRENT_TIMESTAMP?
MysqlTimestampUtcMysql WorkbenchMysql Problem Overview
SEE: https://stackoverflow.com/questions/7291104/update-timestamp-column-in-application-or-database
I'm trying to model something similar in Workbench, but I don't know where to set the "ON UPDATE" part. The best I can get is the following:
-- -----------------------------------------------------
-- Table `foo`.`test`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `foo`.`test` ;
CREATE TABLE IF NOT EXISTS `foo`.`test` (
`test_id` INT NOT NULL ,
`date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`date_updated` TIMESTAMP NOT NULL DEFAULT 0 ,
PRIMARY KEY (`test_id`) )
ENGINE = InnoDB;
Where do I go in Workbench to set up this ON UPDATE part?
Also, I have a rule that all timestamps stored in the database should be UTC. How do I make CURRENT_TIMESTAMP, NOW, etc. be UTC?
Mysql Solutions
Solution 1 - Mysql
I am using MySQL Workbench 5.2.35. Open create/alter table panel, switch to the columns tab, right click on the timestamp field; there you can see possible default
and on update
options.
Important note: You can use CURRENT_TIMESTAMP
as default or updated value for only a single column in a table!
Regarding the UTC question, you can have a look at this question. There is an accepted solution there.
I would suggest you to read MySQL reference manuals as well for Timestamp data type and NOW()
function.
Solution 2 - Mysql
Here is what I do with MySQL Workbench
Data Type: TIMESTAMP Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Solution 3 - Mysql
By default MySQL sets the first timestamp
field to CURRENT_TIMESTAMP
on every UPDATE
or INSERT
statement. I go about designing my schema in a way so that updated_at
is first timestamp
field, so that I don't have to explicitly specify it.