Menu
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?
865
With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP , the column has the current timestamp for its default value and is automatically updated to the current timestamp.
NOW() returns a constant time that indicates the time at which the statement began to execute. NOW() returns the time at which the function or triggering statement began to execute, but SYSDATE() returns the exact time at which it executes. And CURRENT_TIMESTAMP , CURRENT_TIMESTAMP() are synonyms for NOW() .
UPDATE `table_name` is the command that tells MySQL to update the data in a table . SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query. Note, when setting the update values, strings data types must be in single quotes.
To access the MySQL Table Editor, right-click a table name in the Navigator area of the sidebar with the Schemas secondary tab selected and click Alter Table. This action opens a new secondary tab within the main SQL Editor window.
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.
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
