CODEWITHSUNDEEP
mysql
user1516815
user1516815

Reputation: 397

Add date time as default value

Can we use dateadd function when creating a table?

CREATE  TABLE test(
id  INTEGER(16) NOT NULL AUTO_INCREMENT,
CONSTRAINT PRIMARY KEY  (id),
controlstarttime DATETIME NOT NULL DEFAULT NOW(),
timeout DATETIME NOT NULL DEFAULT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 10 SECOND)
 )

I tried above query and it gives me a syntax error. I know this can be done using triggers easily.But I'm wondering why this query doesn't work. Any idea?

Upvotes: 1

Views: 493

Answers (2)

Arun
Arun

Reputation: 760

I think its not possible to create a default interval in mysql, but you can create a trigger

CREATE TRIGGER settime
BEFORE INSERT on table1
FOR EACH ROW BEGIN
   IF new.`date` is null THEN
    SET new.`date` = DATE_ADD(NOW(), INTERVAL 10 SECOND);
    END IF;
END;

Upvotes: 1

alienth
alienth

Reputation: 349

DEFAULT values in MySQL must be constants. They can't be functions or expressions (with the exception of CURRENT_TIMESTAMP).

Source: http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html

Upvotes: 2

Related Questions