CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5471

How to combine date_add with generated always to calculate the dates in a column

DB Fiddle

CREATE TABLE Days (
    id int primary key,
    date_fix VARCHAR(255),
    date_calculated VARCHAR(255) GENERATED ALWAYS AS (DATE_ADD("date_fix", INTERVAL 1 DAY))
);

INSERT Days
(id, date_fix, date_calculated
)
VALUES 
("1", "2019-01-01", ""),
("2", "2019-01-06", ""),
("3", "2019-05-01", ""),
("4", "2019-08-15", ""),
("5", "2019-10-03", "");

In the above table I want to insert a column called date_calculated which calculates the date as following:

date_fix + 1 day

Therefore, I tried to combine GENERATED ALWAYS with DATE_ADD("date_fix", INTERVAL 1 DAY) but I could not make it work so far.

I assume the issue is related to the INSERT statement since I currently use only "" for the column date_calculated but I do not have any clue how to replace this "" in order to achieve that the column is calculated as described.

Do you have any idea how to get the desired table with the calculated column?

Upvotes: 0

Views: 723

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your code works fine with a couple of fixes:

CREATE TABLE Days (
    id int primary key,
    date_fix VARCHAR(255),
    date_calculated VARCHAR(255) GENERATED ALWAYS AS (DATE_ADD(date_fix, INTERVAL 1 DAY))
);

INSERT Days (id, date_fix)
VALUES (1, '2019-01-01'),
       (2, '2019-01-06'),
       (3, '2019-05-01'),
       (4, '2019-08-15'),
       (5, '2019-10-03');

Here is a db<>fiddle.

Your major issue is that you are quite confused by quotes. When writing SQL, it is pretty simple:

  • Use SINGLE quotes for date and string constants.
  • Never use quotes at all for numbers.
  • Do not use DOUBLE quotes (unless you really understand why you are using them). MySQL extends SQL so this is a synonym for single quotes.

The second issue is that there is no need to insert a value into a generated column. The value is calculated when you query the column, not when you insert values.

Then the third issue is types. Do not store dates as strings. MySQL has a wonderful data type to store dates, called date. It was invented for a reason and you should use it.

Upvotes: 1

Tushar
Tushar

Reputation: 568

You should add DATE before DATE_ADD in your query

CREATE TABLE Days (
   id int primary key,
   date_fix VARCHAR(255),
   date_calculated VARCHAR(255) 
   GENERATED ALWAYS AS (DATE(DATE_ADD(date_fix, INTERVAL 1 DAY)))
);

Then you can insert your data

INSERT INTO Days (id, date_fix)
    VALUES ("1", "2019-01-01"),
           ("2", "2019-01-06"),
           ("3", "2019-05-01"),
           ("4", "2019-08-15"),
           ("5", "2019-10-03");

Upvotes: 2

Nitikesh Pattanayak
Nitikesh Pattanayak

Reputation: 84

The approach you are taking to create the columns is not the right approach and not recommended.

The right approach for this logic would be, create the table with Id & date_fix only and then using select statement generate your calculated results. Why to use this method? As, You will be creating a new column unnecessarily to store the information which you can get from select statement will not be scalable enough to work in a large enterprise environment. So always try to minimize the load.

Please find the below code:

CREATE TABLE Days (
    id int primary key,
    date_fix VARCHAR(255)
);

INSERT Days
(id, date_fix
)
VALUES 
('1', '2019-01-01'),
('2', '2019-01-06'),
('3', '2019-05-01'),
('4', '2019-08-15'),
('5', '2019-10-03');

select id,date_fix, DATEADD(DAY,1,CAST(date_fix as DATETIME)) from Days;

Upvotes: 0

Related Questions