0

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.

Catfish
  • 18,876
  • 54
  • 209
  • 353
  • See if [this answer](https://stackoverflow.com/a/52769071/2055998) is helpful. – PM 77-1 May 13 '22 at 16:03
  • Do you mean "24 hours from now" or "the same time tomrrow"? Daylight savings time means they can be different! – Schwern May 13 '22 at 16:04
  • 24 hours from now, disregarding daylight savings. – Catfish May 13 '22 at 16:08
  • @PM77-1 this comment from that answer is https://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql/52769071#comment123614916_69688616. I guess unsupported in mysql 5.7. – Catfish May 13 '22 at 16:09
  • Does this answer your question? [Can I use a function for a default value in MySql?](https://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql) – miken32 May 13 '22 at 17:37

1 Answers1

2

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));
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828