MySQL Workbench: how do I set up "ON UPDATE" and CURRENT_TIMESTAMP?

Question

SEE: 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?

For anyone interested, I created a [Workbench script](https://gist.github.com/pepijnolivier/61ba3d69af2884ef2d6fa107d657e2d5) that automatically adds the timestamp columns on all tables -except pivot tables — Pepijn Olivier, Apr 04 '16 at 21:16

score 161 · Accepted Answer · edited May 23 '17 at 12:26

161

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!

Sample screenshot showing the context menu

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.

edited May 23 '17 at 12:26

Community

1
1

answered Nov 19 '11 at 13:00

melihcelik

4,531
1
21
25

OK, that worked for me. If I wanted to know when a record was soft deleted, I would just create a date_deleted and set that from the application layer, right? Also, any idea about the UTC issue? – StackOverflowNewbie Nov 19 '11 at 13:12
yes, if you are already running a query to mark an item as soft deleted, then it's better to update the date from the application layer as well within the same query (or at least same transaction). I updated the answer for UTC issue. – melihcelik Nov 19 '11 at 13:37
@carbonr Did you read the "important note" on the answer, is that what you ask or do you ask something else? – melihcelik May 04 '12 at 08:44
Thanks for the answer! Worked for me – Ryan Jul 23 '16 at 07:05
2

A small addendum: For me, I had to specify Not Null `NN` before it would auto update from the UI (Workbench 6.0.8) – TemporalWolf Sep 08 '16 at 22:48

score 11 · Answer 2 · answered Oct 31 '16 at 20:08

11

Here is what I do with MySQL Workbench

Data Type: TIMESTAMP Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

answered Oct 31 '16 at 20:08

Cantika Onies

111
1
2

score -2 · Answer 3 · answered Nov 19 '11 at 15:00

-2

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.

answered Nov 19 '11 at 15:00

Ashwini Dhekane

2,280
14
19

MySQL Workbench: how do I set up "ON UPDATE" and CURRENT_TIMESTAMP?

3 Answers3