Automatic Timestamping in MySQL
UPDATED ON: Oct 1, 2020
Setup table schema with automatic timestamping
CREATE TABLE `users` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`full_name` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
-
Automatic properties are specified using the
DEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
clauses in column definitions.- The
DEFAULT CURRENT_TIMESTAMP
clause assignes the current timestamp as the default value of the column. - The
ON UPDATE CURRENT_TIMESTAMP
clause updates the column to the current timestamp when the value of any other column in the row is changed from its current value.
- The
-
CURRENT_TIMESTAMP
is a synonym forNOW()
and returns the current date and time. It returns a constant time that indicates the time at which a statement began to execute.