[mysql] Invalid default value for 'create_date' timestamp field

I have the following sql create statement

mysql> CREATE  TABLE IF NOT EXISTS `erp`.`je_menus` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT ,
    ->   `name` VARCHAR(100) NOT NULL ,
    ->   `description` VARCHAR(255) NOT NULL ,
    ->   `live_start_date` DATETIME NULL DEFAULT NULL ,
    ->   `live_end_date` DATETIME NULL DEFAULT NULL , 
    ->   `notes` VARCHAR(255) NULL ,
    ->   `create_date` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00',
    ->   `created_by` INT(11) NOT NULL ,
    ->   `update_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP  ,
    ->   `updated_by` INT(11) NOT NULL , 
    ->   `status` VARCHAR(45) NOT NULL ,
    ->   PRIMARY KEY (`id`) ) 
    -> ENGINE = InnoDB;

giving following error

ERROR 1067 (42000): Invalid default value for 'create_date'

What is the error here?

This question is related to mysql

The answer is


Change this:

`create_date` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00',
`update_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP  ,

To the following:

`create_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP ,
`update_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

Default values should start from the year 1000.

For example,

ALTER TABLE mytable last_active DATETIME DEFAULT '1000-01-01 00:00:00'

Hope this helps someone.


Just Define following lines at top of your Database SQL file.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

It is working for me.


I try to set type of column as 'timestamp' and it works for me.


You might like to examine the timezone setting on the MySql instance:

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+

in my case, I realised that the underlying system had it's timezone set to BST rather than UTC, and so in the create table the default of '1970-01-01 00:00:01' was being coerced back 1 hour, resulting in an invalid timestamp value.

For me, I actually wanted the machine's timezone set to UTC, and that sorted me out. As I was running Centos/7, I simply did

# timedatectl set-timezone UTC

and restarted everything.


To avoid this issue, you need to remove NO_ZERO_DATE from the mysql mode configuration.

  1. Go to 'phpmyadmin'.
  2. Once phpmyadmin is loaded up, click on the 'variables' tab.
  3. Search for 'sql mode'.
  4. Click on the Edit option and remove NO_ZERO_DATE (and its trailing comma) from the configuration.

This is a very common issue in the local environment with wamp or xamp.


In ubuntu desktop 16.04, I did this:

  1. open file: /etc/mysql/mysql.conf.d/mysqld.cnf in an editor of your choice.

  2. Look for: sql_mode, it will be somewhere under [mysqld].

  3. and set sql_mode to the following:

    NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  4. Save and then restart mysql service by doing:

    sudo service mysql restart


To disable strict SQL mode

Create disable_strict_mode.cnf file at /etc/mysql/conf.d/

In the file, enter these two lines:

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Finally, restart MySQL with this command:

sudo service mysql restart

I was able to resolve this issue on OS X by installing MySQL from Homebrew

brew install mysql

by adding the following to /usr/local/etc/my.cnf

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and restarting MySQL

brew tap homebrew/services
brew services restart mysql

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC (see doc). The default value must be within that range.

Other odd, related, behavior:

CREATE TABLE tbl1 (
    ts TIMESTAMP);  
Query OK, 0 rows affected (0.01 sec)

CREATE TABLE tbl2 (
    ts TIMESTAMP,
    ts2 TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'ts2'

CREATE TABLE tbl3 (
    ts TIMESTAMP,
    ts2 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
Query OK, 0 rows affected (0.01 sec)

Side note, if you want to insert NULLS:

CREATE TABLE tbl4 (
    ts TIMESTAMP NULL DEFAULT NULL);

If you generated the script from the MySQL workbench.

The following line is generated

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Remove TRADITIONAL from the SQL_MODE, and then the script should work fine

Else, you could set the SQL_MODE as Allow Invalid Dates

SET SQL_MODE='ALLOW_INVALID_DATES';

You could just change this:

create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

To something like this:

create_date varchar(80) NOT NULL DEFAULT '0000-00-00 00:00:00',


You could just change this:

`create_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

To something like this:

`create_date` TIMESTAMP NOT NULL DEFAULT '2018-04-01 12:00:00',

Using OS X, install mysql from Homebrew, System Variables based on its compiled-in defaults. Solution is to remove "NO_ZERO_DATE" from System Variables "sql_mode".

Just please keep in mind that scope involve.

If you want to affect only in your session, please use "@@session", For example:

SET @@session.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

In this case, it will not affect once your session ends or your change it. It has not effect on other session.

If you want to affect on all client, please use "@@global", for example:

SET @@global.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

In this case, it only affects on the clients that connect after the change(not affect on current all clients), and will not work once server exit.


I had a similar issue with MySQL 5.7 with the following code:

`update_date` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

I fixed by using this instead:

`update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP