I want to get dates between two dates from MySQL table
EXAMPLE
- fromdate(2015-01-01 and so on....),
- todate(2015-01-10...and so on)
are the columns i have to get dates between these two columns.
How to get this dynamically using MySQL query?
Let's create a query that use startdate and enddate and displays the date list
SELECT @start_date + INTERVAL num DAY dt FROM
(SELECT tth*10000+th*1000+h*100+t*10+u num FROM
(SELECT 0 tth 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) TTHN,
(SELECT 0 th 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) THN,
(SELECT 0 h 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) HN,
(SELECT 0 t 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) TN,
(SELECT 0 u 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) UN) A,
(SELECT '2015-01-01' start_date) B,
(SELECT '2015-01-10' end_date) C
WHERE start_date + INTERVAL num DAY <= end_date
ORDER BY num;
Here is the output
mysql> SELECT @start_date + INTERVAL num DAY dt FROM
-> (SELECT tth*10000+th*1000+h*100+t*10+u num FROM
-> (SELECT 0 tth 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) TTHN,
-> (SELECT 0 th 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) THN,
-> (SELECT 0 h 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) HN,
-> (SELECT 0 t 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) TN,
-> (SELECT 0 u 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) UN) A,
-> (SELECT '2015-01-01' start_date) B,
-> (SELECT '2015-01-10' end_date) C
-> WHERE start_date + INTERVAL num DAY <= end_date
-> ORDER BY num;
+------------+
| dt |
+------------+
| 2015-01-01 |
| 2015-01-02 |
| 2015-01-03 |
| 2015-01-04 |
| 2015-01-05 |
| 2015-01-06 |
| 2015-01-07 |
| 2015-01-08 |
| 2015-01-09 |
| 2015-01-10 |
+------------+
10 rows in set (0.08 sec)
mysql>
Here is how the query works
Subquery A
: Numbers from 0 to 99,999Subquery B
: Start DateSubquery C
: End Datedt
: Date generated from Start Date + any number of days from Subquery A
WHERE start_date + INTERVAL num DAY <= end_date;
: Boundary of the Date ListYou could then LEFT JOIN
this list to your actual MySQL table.
I discussed a similar technique before : get all dates in the current month
In your particular case, if you had from_date
and to_date
in a table called mytable
, the query would go something like this
SELECT B.from_date + INTERVAL num DAY dt FROM
(SELECT tth*10000+th*1000+h*100+t*10+u num FROM
(SELECT 0 tth 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) TTHN,
(SELECT 0 th 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) THN,
(SELECT 0 h 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) HN,
(SELECT 0 t 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) TN,
(SELECT 0 u 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) UN) A,
mytable B
WHERE B.from_date + INTERVAL num DAY <= B.to_date
ORDER BY num;
GIVE IT A TRY !!!