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

Question

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
);

I changed my type to TIMESTAMP and it worked, but now I'm stucked upon creating an index. My query is CREATE INDEX IF NOT EXISTS LINK_INDEX on Table(url_hash,content_hash,date_). If I execute it woithout IF NOT EXISTS, it works fine. Whats the issue>? — Praful Bagai
– Praful Bagai, Commented Mar 10, 2015 at 15:11

Abhik Chakraborty · Accepted Answer · 2015-03-10 15:06:12Z

5

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
) ;

edited Mar 10, 2015 at 15:06

answered Mar 10, 2015 at 14:49

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Praful Bagai Over a year ago

I changed my type to TIMESTAMP and it worked, but now I'm stucked upon creating an index. My query is CREATE INDEX IF NOT EXISTS LINK_INDEX on Table(url_hash,content_hash,date_). If I execute it woithout IF NOT EXISTS, it works fine. Whats the issue>?

Abhik Chakraborty Over a year ago

I usually do as alter table table_name add index some_index_name(col1,col2) you may try the same. Check the doc here dev.mysql.com/doc/refman/5.6/en/create-index.html if not exists does not supported yet

Simon Sellick · Accepted Answer · 2015-03-10 14:47:37Z

0

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".

answered Mar 10, 2015 at 14:47

Simon Sellick

1981 silver badge13 bronze badges

1 Comment

Praful Bagai Over a year ago

I changed my type to TIMESTAMP and it worked, but now I'm stucked upon creating an index. My query is CREATE INDEX IF NOT EXISTS LINK_INDEX on Table(url_hash,content_hash,date_). If I execute it woithout IF NOT EXISTS, it works fine. Whats the issue>?

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related