MySql using interval with a list of dates

Question

I'm willing to fetch all the dates that are listed in the list reducing 1 day I'm trying something like that:

select date
from MyTable
where date in ('2022-03-22', '2022-03-18', '2022-03-11') - interval 1 day

I wish to receive:

'2022-03-21', '2022-03-17', '2022-03-10'

Answer 1

It can be done with intervals.

Here is a link to how it works : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/48

SELECT ADDDATE("2022-03-22", INTERVAL -1 DAY);
SELECT ADDDATE("2022-03-18", INTERVAL -1 DAY);
SELECT ADDDATE("2022-03-11", INTERVAL -1 DAY);

You can also do it with a concat for example : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/49

SELECT CONCAT(ADDDATE("2022-03-22", INTERVAL -1 DAY),' | ',
              ADDDATE("2022-03-18", INTERVAL -1 DAY),' | ',
              ADDDATE("2022-03-11", INTERVAL -1 DAY));

Answer 2

You can add 1 day to the date column's value to compare it against the IN list:

SELECT date
FROM MyTable
WHERE date + INTERVAL 1 DAY IN ('2022-03-22', '2022-03-18', '2022-03-11');