-1

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?

0

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,999
  • Subquery B : Start Date
  • Subquery C : End Date
  • dt : Date generated from Start Date + any number of days from Subquery A
  • WHERE start_date + INTERVAL num DAY <= end_date; : Boundary of the Date List

You 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 !!!

  • it is taking more tha an hour to execute the results for 14000 records. is there any another way using while loop or joins for the same scenario? – abc Sep 16 '15 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.