Use CURRENT_TIMESTAMP as Default in MySQL

  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

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

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - MySQL Timestamp

  • Insert Timestamp Into a MySQL Table
  • Compare MySQL Timestamp Dates With the Date Parameter Only
  • Convert Timestamp Field to Date in Mysql Query