Skip to content

Instantly share code, notes, and snippets.

@peterwake
Created November 3, 2020 12:50
Show Gist options
  • Save peterwake/b67eafb9d248091024d2ad782a5e50f5 to your computer and use it in GitHub Desktop.
Save peterwake/b67eafb9d248091024d2ad782a5e50f5 to your computer and use it in GitHub Desktop.
MySQL query to fill in missing dates with a sparse result set
/* For a query of results in the last 31 days */
SELECT recent_dates.`date`,
IFNULL(sparse_results.`count`, 0) AS `count`
FROM
(SELECT SUBDATE(CURDATE(), number_table.delta) AS `date`
FROM
(SELECT 0 delta
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
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20
UNION SELECT 21
UNION SELECT 22
UNION SELECT 23
UNION SELECT 24
UNION SELECT 25
UNION SELECT 26
UNION SELECT 27
UNION SELECT 28
UNION SELECT 29
UNION SELECT 30
UNION SELECT 31) AS number_table) AS recent_dates
LEFT JOIN
(SELECT FROM_UNIXTIME(activity_completed, '%Y-%m-%d') AS `date`,
COUNT(id) AS `count`
FROM mytable
GROUP BY `date`
ORDER BY `date` DESC
LIMIT 33) AS sparse_results ON recent_dates.`date` = sparse_results.`date`
ORDER BY recent_dates.`date` ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment