CODEWITHSUNDEEP
mysqlsqlmariadbdateadd
AmirTheFree
AmirTheFree

Reputation: 66

MySQL DATE_ADD function; how to use another value of another column as date argument on table creation

I have a table for doing s.th with weeks, I want the end column to automatically set its value 6 days after the start column but I don't know how to refer to start.

CREATE TABLE `weeks`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `start` DATE UNIQUE NOT NULL,
    `end` DATE UNIQUE NOT NULL DEFAULT DATE_ADD(`start`, INTERVAL 6 DAY), -- this line
    `availablity` INT NOT NULL
);

could anyone help ?

Upvotes: 0

Views: 269

Answers (1)

nbk
nbk

Reputation: 49375

you have the wrong SYNTAX

CREATE TABLE `weeks`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `start` DATE UNIQUE NOT NULL,
    `end` DATE   GENERATED ALWAYS AS (DATE_ADD(`start`, INTERVAL 6 DAY)) UNIQUE,
    `end2` DATE  NOT NULL UNIQUE DEFAULT  (DATE_ADD(`start`, INTERVAL 6 DAY)) ,
    `availablity` INT NOT NULL
);

INSERT INTO weeks (`start`,`availablity`) VALUEs (NOW(),1)

SELECT * FROm weeks

id | start      | end        | end2       | availablity
-: | :--------- | :--------- | :--------- | ----------:
 1 | 2021-06-25 | 2021-07-01 | 2021-07-01 |           1

db<>fiddle here

Upvotes: 1

Related Questions