How to fetch data between two column of date type

Question

I have a table for storing events data, that is containing following two entries

id | title  | startdate  | enddate
1  | event1 | 2019-04-01 | 2019-04-04
2  | event2 | 2019-04-04 | 2019-04-05 

I need to write a query to fetch the data as

id| title  | date    
1 | event1 | 2019-04-01
1 | event1 | 2019-04-02
1 | event1 | 2019-04-03
1 | event1 | 2019-04-04
1 | event2 | 2019-04-04
1 | event2 | 2019-04-05

Please suggest any query.

Mysql query please.

Answer 1

You can do without creating table. This query below is to make a date range without creating or referring to any table:

SELECT 1 as 'tempid',CONCAT_WS('-','2019-04',LPAD((a+b),2,0)) AS 'dates' 
FROM
(SELECT 0 a UNION 
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 5 UNION 
SELECT 6 UNION 
SELECT 7 UNION 
SELECT 8 UNION 
SELECT 9) a, 
(SELECT 0 b UNION 
SELECT 10 UNION 
SELECT 20 UNION 
SELECT 30) dd;

Note that if you run the query above, you'll get date range between 2019-04-00 and 2019-04-39.

SELECT t.id,t.title,d.dates FROM
(SELECT 1 as 'tempid',CONCAT_WS('-','2019-04',LPAD((a+b),2,0)) AS 'dates' 
FROM
(SELECT 0 a UNION 
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 5 UNION 
SELECT 6 UNION 
SELECT 7 UNION 
SELECT 8 UNION 
SELECT 9) a, 
(SELECT 0 b UNION 
SELECT 10 UNION 
SELECT 20 UNION 
SELECT 30) d) d LEFT JOIN 
-- this part here is your query from the table
(SELECT id,title,startdate,enddate,1 AS tempid
FROM your_table) t 
ON d.tempid=t.tempid WHERE d.dates BETWEEN startdate AND enddate;

EDIT: If you want whole year of dates without creating table, you can use this query:

SELECT CONCAT_WS('-',yy,LPAD(m,2,0),LPAD(days,2,0)) dates FROM
(SELECT 1 AS id,a+b AS 'days' FROM
(SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) b) d LEFT JOIN 
(SELECT 1 AS id,m FROM
(SELECT 1 m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) m) m ON d.id=m.id LEFT JOIN 
-- This part here > YEAR(CURDATE())=2019. 
(SELECT 1 AS id, YEAR(CURDATE() - INTERVAL 1 YEAR) yy
-- Adding '- INTERVAL 1 YEAR' means that it will reduce 1 year or YEAR(CURDATE()). 
-- Hence it will become '2018'. 
-- If you want future year, just change it to '+ INTERVAL 1 YEAR' = 2020 or '+ INTERVAL 2 YEAR' = 2021. 
) yy ON d.id=yy.id WHERE days BETWEEN 1 AND 31 ORDER BY dates;

Please take note that this is just a temporary table use for checking as the dates here will all be between 1 and 31. It means that the last date for every month is 201X-XX-31 regardless if it's February or June. The main purpose here is to use this for a quick comparison only. There are ways to make sure that all these dates will follow the correct date in the calendar but the query will be complex.

Answer 2

Solution of your problem can be solved by creating a table having dates of around 273 years. Below query creates the table:

CREATE TABLE test2 AS 
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9);

CREATE TABLE calendar AS 
select adddate('1900-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) sdate 
from test2 t0,test2 t1,test2 t2,test2 t3,test2 t4;

Range of Calendar Table which you can change and can increase also:

MIN(sdate)    MAX(sdate)
1900-01-01    2173-10-15

Using The above table you can generate dates between the required range. Below query gives your solution:

SELECT id,title,c.sdate as "date" 
FROM test t 
INNER JOIN calendar c 
ON c.sdate BETWEEN t.startdate AND t.enddate;

Output:

id  title   date
1   event1  2019-04-01
1   event1  2019-04-02
1   event1  2019-04-03
1   event1  2019-04-04
2   event2  2019-04-04
2   event2  2019-04-05

For demo follow below link:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6ebd669acae67d23cb5b6b9d04c86a10

Answer 3

You can join two column into one.. there is no such method like that..

SELECT id,title,concat(startdate  ,", ",enddate) as date FROM `table` 

Result would be

id| title  | date    
1 | event2 | 2019-04-04, 2019-04-05
2 | event1 | 2019-04-01, 2019-04-02

Now you can separate "date" into two string/column by php or whatever the language is..