How to correctly compose a query to select days from a range of dates?

Question

MySQL

The table stores cases that have a start date and an end date (field type DATETIME).
id | date_from | date-_to
---------------------------------------------------- -------------
1 | 2015-11-02 00:00:00 | 2015-11-02 23:59:59
2 | 2015-11-02 09:00:00 | 2015-11-02 18:00:00
3 | 2015-11-02 00:00:00 | 2015-12-01 23:59:59
4 | 2016-01-01 00:00:00 | 2016-01-01 23:59:59
I need to display the days on which these tasks are relevant

SELECT *, DATE_FORMAT(date_from, '%m-%d-%Y') AS date_from_date FROM `task` GROUP BY date_from_date

Such a request is not suitable, because only the start days are selected, and I need all the days in this range.
As a result, I need to get the dates (days) on which the tasks are relevant.
In this example, the query should return:
2015-11-02
2015-11-03
2015-11-04
2015-11-05
...
2015-12-01
2016-01-01

Answer 1

SELECT DATE_ADD(date_from, INTERVAL rn DAY) AS `date`
FROM task
INNER JOIN (
  SELECT @rn := @rn + 1 AS rn
  FROM (
    SELECT *
    FROM (
      SELECT 1 AS n1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
    ) AS t1 CROSS JOIN (
      SELECT 1 AS n2 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ) AS t2
  ) AS t CROSS JOIN (SELECT @rn := -1) AS var   
) AS tally ON DATEDIFF(date_to, date_from) >= rn
ORDER BY `date`

Answer 2

Using Google search

Answer 3

SELECT
GROUP_CONCAT (id),
DATE_FORMAT(date_from, '%m-%d-%Y') AS date_from_date,
DATE_FORMAT(date_to, '%m-%d-%Y') AS date_to_date
FROM `task`
GROUP BY DATE_FORMAT(date_from , '%m-%d-%Y') ,
DATE_FORMAT(date_to, '%m-%d-%Y')

Answer 4

What do you want to receive? string with dates for each task? then:

DELIMITER $$
--
-- Create function "calc_date_range"
--
CREATE FUNCTION calc_date_range(date_start DATE, date_end DATE)
RETURNS text CHARSET latin1
SQL SECURITY INVOKER
BEGIN
DECLARE res_text text;
DECLARE tmp_date date;
IF(date_end < date_start) THEN
RETURN '';
END IF;
SET tmp_date := date_start;
SET res_text :='';
WHILE(tmp_date<=date_end) DO
SET res_text := CONCAT(res_text,',',DATE_FORMAT(tmp_date, '%m-%d-%Y'));
SET tmp_date := DATE_ADD(tmp_date, INTERVAL 1 DAY);
END WHILE;
RETURN SUBSTRING(res_text,2);
END

Or you want to get a list of days for all tasks under a condition. To do this, you will have to fill in the "calendar" table (for example, with the fields (`cdate`,`weekend`,`lolshta`))
and receive a request like:

SELECT c.cdate FROM tasks t
INNER JOIN calendar c ON c.cdate >= DATE(t.date_from) AND c.cdate<=DATE(t.date_to)
WHERE
t.USER_ID = 17
AND t.status = 4
AND NOT c .weekend = 1
GROUP BY c.cdate;