Zhi Zhi - 6 months ago 11
SQL Question

mysql select where -- repeats every nth day from the start date - between two dates

Looks so simple, but it's actually so hard, at least for me.

What i have:
mysql scheme similar to :

start_date | end_date | interval
--------------------------------
2016-05-01 00:00:00 | 2016-05-26 00:00:00 | 3


What i want to achieve as results:
Adding something that actually makes this hard.
I want to get these results depending on the current date. So let's say today is 2016-05-05. The results that i want are as follows:

*2016-05-01* <- past date so no match
*2016-05-04* <- past date so no match
**2016-05-07**
**2016-05-10**
**2016-05-13**
etc...


I don't want temp tables if possible.

Modulus doesn't work for me, at least for all the different ways i've tried it.

Answer

Generally, issues of data display are best handled in application level code, but just for fun...

Note that I'm use a table of integers, but you can use SELECT 1 i UNION SELECT 2 UNION ... etc instead if you like...

DROP TABLE IF EXISTS my_table;


CREATE TABLE my_table(start_date DATE,end_date DATE,date_interval INT NOT NULL);

INSERT INTO my_table VALUES
('2016-05-01','2016-05-26',3);

SELECT * FROM my_table;
+------------+------------+---------------+
| start_date | end_date   | date_interval |
+------------+------------+---------------+
| 2016-05-01 | 2016-05-26 |             3 |
+------------+------------+---------------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+   


SELECT start_date + INTERVAL i*date_interval DAY dt FROM my_table,ints WHERE start_date + INTERVAL i*date_interval DAY <= end_date;
+------------+
| dt         |
+------------+
| 2016-05-01 |
| 2016-05-04 |
| 2016-05-07 |
| 2016-05-10 |
| 2016-05-13 |
| 2016-05-16 |
| 2016-05-19 |
| 2016-05-22 |
| 2016-05-25 |
+------------+