Archive for October, 2021


Automatic Timestamping in MySQL

Setup table schema with automatic timestamping

1
2
3
4
5
6
7
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 assigns 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.

 

This was shamelessly stolen :X  I really liked their formatting, and just wanted to make a backup
https://habenamare.com/guides/mysql/automatic-timestamping/

Permalink » No comments