MySQL how to fill missing dates in range?
MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
Create a table that only holds incrementing numbers - easy to do using an auto_increment:
DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Populate the table using:
INSERT INTO `example`.`numbers` ( `id` ) VALUES ( NULL )...for as many values as you need.
Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, YYYY-MM-DD) -
SELECT `x`.* FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) FROM `numbers` `n` WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) xLEFT JOIN onto your table of data based on the time portion:
SELECT `x`.`ts` AS `timestamp`, COALESCE(`y`.`score`, 0) AS `cnt` FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts` FROM `numbers` `n` WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
If you want to maintain the date format, use the DATE_FORMAT function:
DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`
Time went by since this question was asked. MySQL 8.0 was released in 2018 and added support for recursive common table expressions, which provide an elegant, state-of-the-art solution to this question.
The following query can be used to generate a list of dates, say for the first 15 days of August 2010:
with recursive all_dates(dt) as (
-- anchor
select '2010-08-01' dt
union all
-- recursion with stop condition
select dt + interval 1 day from all_dates where dt < '2010-08-15'
)
select * from all_dates order by dt
You can then left join this resultset with your table to generate the expected output:
with recursive all_dates(dt) as (
select '2010-08-01' dt
union all
select dt + interval 1 day from all_dates where dt < '2010-08-15'
)
select d.dt date, coalesce(t.score, 0) score
from all_dates d
left join mytable t on t.date = d.dt
order by d.dt
Demo on DB Fiddle:
date | score :--------- | ----: 2010-08-01 | 19 2010-08-02 | 21 2010-08-03 | 0 2010-08-04 | 14 2010-08-05 | 0 2010-08-06 | 0 2010-08-07 | 10 2010-08-08 | 0 2010-08-09 | 0 2010-08-10 | 14 2010-08-11 | 0 2010-08-12 | 0 2010-08-13 | 0 2010-08-14 | 0 2010-08-15 | 0
Note that it is very easy to adapt the recursive CTE for other intervals or periods. As an example, say we want a row every 15 minutes from 4 AM to 8 AM on August 1st, 2010 ; we can do :
with recursive all_dates(dt) as (
select '2010-08-01 04:00:00' dt
union all
select dt + interval 15 minute from all_dates where dt < '2010-08-01 08:00:00'
)
...
I'm not a fan of the other answers, requiring tables to be created and such. This query does it efficiently without helper tables.
SELECT
IF(score IS NULL, 0, score) AS score,
b.Days AS date
FROM
(SELECT a.Days
FROM (
SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
ON date = b.Days
ORDER BY b.Days;
So lets dissect this.
SELECT
IF(score IS NULL, 0, score) AS score,
b.Days AS date
The if will detect days that had no score and set them to 0. b.Days is the configured amount of days you chose to get from the current date, up to 1000.
(SELECT a.Days
FROM (
SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
This subquery is something I saw on stackoverflow. It efficiently generates a list of the past 1000 days from the current date. The interval (currently 30) in the WHERE clause at the end determines which days are returned; the maximum is 1000. This query could be easily modified to return 100s of years worth of dates, but 1000 should be good for most things.
LEFT JOIN your_table
ON date = b.Days
ORDER BY b.Days;
This is the part that brings your table that contains the score into it. You compare to the selected date range from the date generator query to be able to fill in 0s where needed (the score will be set to NULL initially, because it is a LEFT JOIN; this is fixed in the select statement). I also order it by the dates, just because. This is preference, you could also order by score.
Before the ORDER BY you could easily join with your table about user info you mentioned with your edit, to add that last requirement.
I hope this version of the query helps someone. Thanks for reading.