search
Search
Login
Unlock 100+ guides
menu
menu
web
search toc
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
What does this mean?
Why is this true?
Give me some examples!
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to

MySQL | Automatic Initialization and updating for TIMESTAMP and DATETIME

schedule Aug 12, 2023
Last updated
local_offer
MySQL
Tags
mode_heat
Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!

For TIMESTAMP and DATETIME columns in a table in MySQL you can:

  • Assign the current timestamp as the default value

  • Assign the auto-update value

  • Assign both of the above

NOTE

Auto-update means when the value of any other column in the row is changed, the value of the auto-update column is automatically updated to the current timestamp.

Default value

To set the default value of TIMESTAMP and DATETIME columns to be the current timestamp:

CREATE TABLE table_name (
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
column2 DATETIME DEFAULT CURRENT_TIMESTAMP
);

Note that the default value does not have to be CURRENT_TIMESTAMP, you can use a fixed value also:

CREATE TABLE table_name (
column1 TIMESTAMP DEFAULT '2020-01-01 00:00:00',
column2 DATETIME DEFAULT '2020-01-01 00:00:00'
);

Auto-update value

To set the TIMESTAMP and DATETIME columns to auto-update:

CREATE TABLE table_name (
column1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
column2 DATETIME ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
NOTE

If we do not provide a DEFAULT clause, TIMESTAMP defaults to 0, while DATETIME defaults to NULL

You can modify this behavior by specifying NULL or NOT NULL attributes:

CREATE TABLE table_name (
column1 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- default NULL
column2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

Now column1 will have a default value of NULL while column2 will have a default value of 0.

Default value and Auto-update

To set the TIMESTAMP and DATETIME datatype columns to have a default value and auto-update:

CREATE TABLE table_name (
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
column2 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Now column1 and column2 both have a default value (current timestamp), while they will also be auto-updated when changes are made to any other column in the table (with the timestamp of when the change was made).

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
1
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!