CODEWITHSUNDEEP
mysqldatabase
Jose Luis Martinez
Jose Luis Martinez

Reputation: 5

Add days on INSERT MySQL

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!

Upvotes: 0

Views: 36

Answers (2)

PressingOnAlways
PressingOnAlways

Reputation: 12356

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562358

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

Upvotes: 1

Related Questions