CODEWITHSUNDEEP
mysqlmysql-5.7
Catfish
Catfish

Reputation: 19324

Create table and set default value to NOW() + 24 hours

I'm trying to create a table and set the default value to now() + 24 hours. I'm getting syntax errors when trying to do this.

This doesn't work

CREATE TABLE IF NOT EXISTS `my_table` (
    `my_table_id` CHAR(36) BINARY NOT NULL , 
    `expiration_time` DATETIME NOT NULL DEFAULT (NOW() + INTERVAL 24 HOUR),
    PRIMARY KEY (`my_table_id`)
) ENGINE=InnoDB;

Although this does work SELECT NOW() + INTERVAL 24 HOUR; so i'm not sure why it doesn't work when trying to create a table.

Upvotes: 0

Views: 410

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562991

Expressions for defaults are not supported in MySQL 5.7.

You can implement a "default" expression in a trigger such as the following:

CREATE TRIGGER my_awesome_trigger BEFORE INSERT ON my_table
FOR EACH ROW
  SET NEW.expiration_time = COALESCE(NEW.expiration_time, NOW() + INTERVAL 24 HOUR));

Upvotes: 2

Related Questions