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 and ON 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.

  • CURRENT_TIMESTAMP is a synonym for NOW() and returns the current date and time. It returns a constant time that indicates the time at which a statement began to execute.

Links