CODEWITHSUNDEEP
mysql
Praful Bagai
Praful Bagai

Reputation: 17382

Create a column 'Date' with default value current datetime MYSQL

Following is my SQL query, it throws an error:-

CREATE TABLE IF NOT EXISTS USER_PROFILE(Id INT PRIMARY KEY AUTO_INCREMENT, date DATETIME NOT NULL DEFAULT NOW) ;

It says Invalid default value for 'date'.

I've tried synonyms for NOW() as well, namely CURRENT_TIMESTAMP, but still the same error.

How can I create a column date with default value current time?

On the documentation page, it says to assign this way

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

Upvotes: 1

Views: 3891

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

From the document

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns

So no function is allowed in the default value hence the first query is failing.

Again from the document

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.

Before 5.6.5, this is true only for TIMESTAMP

So your mysql version is less than 5.6.5 hence the 2nd query is failing too.

So you need to create the table as

CREATE TABLE IF NOT EXISTS 
USER_PROFILE
(
  Id INT PRIMARY KEY AUTO_INCREMENT, 
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ;

Upvotes: 5

Simon Sellick
Simon Sellick

Reputation: 198

It might be that DATE, as a reserved word, is confusing it by the time it gets to the DEFAULT clause. Try a different name and if that works, try quoting "date".

Upvotes: 0

Related Questions