Displaying all the dates between two dates

Question

I have a table of the form:

date       hrs
01-01-2020  5
03-01-2020  1
05-01-2020  2

I want to get all the dates from 01-01-2020 to 05-01-2020 as output when I use the mysql command in php:

date       hrs
01-01-2020  5
02-01-2020  0
03-01-2020  1
04-01-2020  0
05-01-2020  2

I have used the following statement:

SELECT dates FROM booking WHERE date BETWEEN '$start' AND '$end'"

where start date is 01-01-2020 and end date is 05-01-2020. But I got the first table format not the second one.

Given that you have access to application code, just handle this logic there. Note that dates in sql adhere to a specific format, so fix that first. — Strawberry, Jan 15 '20 at 08:32

score 0 · Accepted Answer · edited Jan 15 '20 at 08:31

First, you could prepare all the dates between the two specified dates. Referring to this answer, the subquery would be:

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (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) t0,
 (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) t1,
 (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) t2,
 (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) t3,
 (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) t4) v
where selected_date between '$start' AND '$end'

Then I would left join this table to the table you have, as follows:

select v.selected_date , coalesce(booking.hrs, 0) from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (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) t0,
 (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) t1,
 (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) t2,
 (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) t3,
 (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) t4) v
left join booking on v.selected_date = booking.date
where v.selected_date between '$start' AND '$end'

I'm not sure if there's a simpler answer, but this worked for me.

The link above has explanations to how the subquery can obtain all dates between two given dates. Hope this helps!

Generate 100000 dates for to select only 5 from them? Monsieur knows a lot about perversions... — Akina, Jan 15 '20 at 07:19
OP just provided a format, I was trying to be conservative and stick to a well-accepted answer in existence :) — wookiekim, Jan 15 '20 at 07:23

score 0 · Answer 2 · answered Jan 15 '20 at 07:17

If your MySQL server version is 8+ use

WITH RECURSIVE 
cte AS ( SELECT MIN(dates) dates
         FROM booking 
         UNION ALL
         SELECT dates + INTERVAL 1 DAY
         FROM cte
         WHERE dates < ( SELECT MAX(dates)
                         FROM booking ) )
SELECT dates, hrs
FROM booking
UNION ALL
SELECT dates, 0
FROM cte
WHERE NOT EXISTS ( SELECT NULL
                   FROM booking
                   WHERE booking.dates = cte.dates )
ORDER BY dates

score 0 · Answer 3 · answered Jan 18 '20 at 06:44

most easily you can use like this :


SET @date_min = '2020-01-01';
SET @date_max = '2020-01-05';

SELECT
   date_generator.date as dates,
   IFNULL(hrs, 0) as hrs
from (
   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator
left join booking on DATE(`date`) = date_generator.date
GROUP BY `date`;

so here I am creating a temporary table date_generator will dates in between of given date range and join to with your main table (transactions).

output as expected:

dates      | hrs 
01-01-2020 | 5
02-01-2020 | 0
03-01-2020 | 1
04-01-2020 | 0
05-01-2020 | 2

score 0 · Answer 4 · answered Jan 18 '20 at 06:49

0

SELECT * FROM booking 
WHERE date >= '2020-01-01 00:00:00' 
AND date <= '2020-05-01 00:00:00'

answered Jan 18 '20 at 06:49

Tom Shaw

1,642
2
16
25

Displaying all the dates between two dates

4 Answers4