Add days on INSERT MySQL-CodePudding

I am (unsuccessfully) trying to set as default value to DATETIME a date based on another column that uses CURRENT_TIMESTAMP as default, but adding some days.

On MSSQL I used to do (dateadd(day,(5),[start_date])) as a "Computed Column Specification" to set the column end_date 5 days more that start_date column.

So, when I perform an INSERT I would like that start_date were set to NOW(); and end_date were set to [NOW(); X days]

Is this even possible on MySQL?

Thanks in advance!

CodePudding user response：

If you use an older version of MySQL and cannot use expressions in the DEFAULT clause as shown in the other answer, you can do this with a trigger.

CREATE TRIGGER mytrigger BEFORE INSERT ON t1
FOR EACH ROW BEGIN
  SET NEW.start_date = CURDATE();
  SET NEW.end_date = CURDATE()   INTERVAL 5 DAY;
END

CodePudding user response：

As of MySQL 8.0.13, you can use expressions for default values.

It would allow you to call functions in your default. However, I do not believe you have the ability to query other columns (give it a try?)

You can use something like:

CREATE TABLE t1 (
  ...
  start_date DATE      DEFAULT (CURRENT_DATE),
  end_date DATE        DEFAULT (CURRENT_DATE   INTERVAL 5 DAY),
  ...
);

Do note the enclosing parenthesis is required for indicating it is an expression and not a literal.

Reference: https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html