Search code examples
mysqltimestampdefault

Unable to set current timestamp as default

I am trying to set CURRENT_TIMESTAMP as default value for my DATE type in phpmyadmin. I first tried to set DATE type, and then to DATETIME type, but i'm getting this error: 

#1067 - Invalid default value for 'registered_at'

So, how can I set current timestamp as default value ?

Solution

  • You need to use MySQL Server 5.6.5 or later to do this. You also need to use the TIMESTAMP or DATETIME data types. It won't work with DATE.

    The example in the manual shows both TIMESTAMP and DATETIME:

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

    If you have an earlier version of MySQL Server, you need to use the TIMESTAMP data type only. It won't work with DATETIME.

    Read https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html for more information.