How to Use CURRENT_TIMESTAMP as Default in MySQL

  2. HowTo
  3. MySQL Howtos
  4. How to Use CURRENT_TIMESTAMP as Default …
Habdul Hazeez Feb 02, 2024
MySQL MySQL Timestamp
  1. Reproduce the Error When Using CURRENT_TIMESTAMP as DEFAULT in MySQL
  2. Reorder the Table Columns to Use CURRENT_TIMESTAMP as DEFAULT in MySQL
  3. Use DEFAULT 0 to Use CURRENT_TIMESTAMP as DEFAULT in MySQL
  4. Use a Time Value to Use CURRENT_TIMESTAMP as DEFAULT in MySQL
How to Use CURRENT_TIMESTAMP as Default in MySQL

This article teaches you how to use CURRENT_TIMESTAMP as DEFAULT in the MySQL versions less than 5.6.5. As a result, you can prevent MySQL Error 1293.

Our methods include reordering the table columns and using DEFAULT 0 and a time value.

Reproduce the Error When Using CURRENT_TIMESTAMP as DEFAULT in MySQL

Let’s reproduce the error before showing you how to use CURRENT_TIMESTAMP as DEFAULT. This is the error you’ll get when you try to use CURRENT_TIMESTAMP as DEFAULT.

To follow along, do the following:

  • Download the MySQL version less than 5.6.5. You’ll get this as part of XAMPP 1.8.0, or other means.
  • Open MySQL in XAMPP, log in with mysql -u root -p. You’ll notice the MySQL version is 5.5.2a, so it’s less than 5.6.5.
  • Create a database called just_a_test_db.

Now, use the following SQL to create a site_users table in the database:

CREATE TABLE site_users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    user_firstname VARCHAR(255) NOT NULL,
    user_surname VARCHAR(255) NOT NULL,
    user_email_address VARCHAR(255) NOT NULL UNIQUE,
    user_password CHAR(40) NOT NULL,
    is_active BOOL NOT NULL DEFAULT FALSE,
    is_validated BOOL NOT NULL DEFAULT FALSE,
    date_validated TIMESTAMP,
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
) Engine=InnoDB;

You’ll get an error as seen in the following image:

MySQL Error 1293 in MySQL 5.5.25a

Reorder the Table Columns to Use CURRENT_TIMESTAMP as DEFAULT in MySQL

You can use CURRENT_TIMESTAMP as DEFAULT if you reorder the table columns in your SQL query. The column with a CURRENT_TIMESTAMP as DEFAULT should come first; other columns with TIMESTAMP values should follow.

In the following SQL, we’ve reordered the SQL, and MySQL creates the table:

CREATE TABLE site_users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    user_firstname VARCHAR(255) NOT NULL,
    user_surname VARCHAR(255) NOT NULL,
    user_email_address VARCHAR(255) NOT NULL UNIQUE,
    user_password CHAR(40) NOT NULL,
    is_active BOOL NOT NULL DEFAULT FALSE,
    is_validated BOOL NOT NULL DEFAULT FALSE,
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    date_validated TIMESTAMP,
    PRIMARY KEY (user_id)
) Engine=InnoDB;

Output in the MySQL console:

Successfully created database table after reordering table columns

Use DEFAULT 0 to Use CURRENT_TIMESTAMP as DEFAULT in MySQL

Using DEFAULT 0 is another option that’ll allow you to use CURRENT_TIMESTAMP as DEFAULT. You’ll apply it to TIMESTAMP columns that do not have a default value.

From our SQL, this is the date_validated column. The following SQL shows you how to do this:

CREATE TABLE site_users_2 (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    user_firstname VARCHAR(255) NOT NULL,
    user_surname VARCHAR(255) NOT NULL,
    user_email_address VARCHAR(255) NOT NULL UNIQUE,
    user_password CHAR(40) NOT NULL,
    is_active BOOL NOT NULL DEFAULT FALSE,
    is_validated BOOL NOT NULL DEFAULT FALSE,
    date_validated TIMESTAMP NOT NULL DEFAULT 0,
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
) Engine=InnoDB;

Output in the MySQL console:

Using DEFAULT 0 on a TIMESTAMP column in MySQL 5.5.25a

Use a Time Value to Use CURRENT_TIMESTAMP as DEFAULT in MySQL

If the default of TIMESTAMP is a time, you can use CURRENT_TIMESTAMP as DEFAULT on other columns. This time value is 0000-00-00 00:00:00, and we’ve used it on the date_validated column.

The following SQL shows this in action:

CREATE TABLE site_users_3 (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    user_firstname VARCHAR(255) NOT NULL,
    user_surname VARCHAR(255) NOT NULL,
    user_email_address VARCHAR(255) NOT NULL UNIQUE,
    user_password CHAR(40) NOT NULL,
    is_active BOOL NOT NULL DEFAULT FALSE,
    is_validated BOOL NOT NULL DEFAULT FALSE,
    date_validated TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
) Engine=InnoDB;

Output in the MySQL console:

Using a time value for a TIMESTAMP column in MySQL 5.5.25a

Author: Habdul Hazeez
Habdul Hazeez avatar Habdul Hazeez avatar

Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.

LinkedIn

Related Article - MySQL Timestamp