Sling Academy
Home/MySQL/DEFAULT & ON UPDATE TIMESTAMP in MySQL 8: Explained with examples

DEFAULT & ON UPDATE TIMESTAMP in MySQL 8: Explained with examples

Last updated: January 26, 2024

Introduction

Working with date and time data is a common requirement in software development. MySQL 8, a widely used database management system, provides several functionalities to make handling temporal data easier, and two of these handy features are ‘DEFAULT TIMESTAMP’ and ‘ON UPDATE TIMESTAMP’. This tutorial will explain how to use these features effectively, with examples to showcase them in action.

Understanding DEFAULT TIMESTAMP

The DEFAULT TIMESTAMP feature allows you to automatically set a default value for a TIMESTAMP column at the time of record insertion when no specific value is provided. The most common default is the current timestamp, which captures the exact date and time a record is added to the database.

Basic Example

Here’s a simple example of how to create a table that automatically populates a created_at field with the current timestamp whenever a new record is inserted:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

The above SQL statement defines a created_at column that will default to the timestamp at the moment a row is inserted, in case a specific timestamp is not provided during the insert operation.

Inserting Data Without Specifying Timestamp

When inserting data without specifying the created_at column:

INSERT INTO users (username) VALUES ('john_doe');

As you can see, we did not specify the created_at column. MySQL will automatically set the current timestamp as its value. To check the inserted data along with its created_at timestamp, you could use:

SELECT * FROM users;

You should see that the created_at column for the john_doe record has been populated with the timestamp that corresponds to the insertion time.

Working with ON UPDATE TIMESTAMP

The ON UPDATE TIMESTAMP feature automatically updates the timestamp column to the current timestamp whenever a record is updated, provided that other columns in the row are also modified.

Basic Example

Consider the following table with an updated_at column that utilizes ON UPDATE TIMESTAMP:

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

This SQL statement creates a updated_at column that defaults to the current timestamp on insert, and also updates to the current timestamp whenever the row is modified.

Updating Data and Viewing Timestamp Changes

After inserting an item:

INSERT INTO products (name, price) VALUES ('Gaming Mouse', 49.99);

If we were to update the price of the ‘Gaming Mouse’:

UPDATE products SET price = 54.99 WHERE name = 'Gaming Mouse';

Querying the data again with:

SELECT * FROM products;

You’ll see that the updated_at column reflects the timestamp of when the last update occurred.

Advanced Usage and Considerations

In more complex database designs, you may want to have multiple timestamp columns for different events, such as for soft deletes, approved_at timestamps, etc. To use DEFAULT TIMESTAMP and ON UPDATE TIMESTAMP in these scenarios, you need to consider the rules and constraints set by MySQL, such as the restriction on having multiple columns with CURRENT_TIMESTAMP as the default value.

As of MySQL 8, these limitations have been mostly lifted, allowing for more flexibility. Still, it’s important to use these features judaiciously to avoid confusion and maintain clear data models.

Combining DEFAULT and ON UPDATE TIMESTAMP

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP,
  PRIMARY KEY (id)
);

The example shows a scenario where both created_at and updated_at columns are used in conjunction with DEFAULT and ON UPDATE TIMESTAMP, whereas deleted_at can be set to a timestamp using a manual update, usually to signal a soft deletion.

Conclusion

Implementing DEFAULT TIMESTAMP and ON UPDATE TIMESTAMP within MySQL 8 can greatly simplify the process of tracking when records are created and modified. By understanding and utilizing these features, developers can ensure data consistency and integrity, reducing the need for manual timestamp entries and the potential for errors that accompany them.

Next Article: CURDATE() and CURRENT_DATE() in MySQL 8: Explained with examples

Previous Article: MySQL 8: How to select rows from the last 7 days, 30 days, etc.

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples