How to fill date gaps in MySQL?

by

You’ll need to create a helper table and fill it with all dates from start to end, then just LEFT JOIN with that table:

SELECT  d.dt AS date,
        COUNT(*) AS total,
        SUM(attitude="positive") AS positive,
        SUM(attitude="neutral") AS neutral,
        SUM(attitude="negative") AS negative
FROM    dates d
LEFT JOIN
        messages m
ON      m.posted_at >= d.dt
        AND m.posted_at < d.dt + INTERVAL 1 DAYS
        AND spam = 0
        AND duplicate = 0
        AND ignore = 0
GROUP BY
        d.dt
ORDER BY
        d.dt

Basically, what you need here is a dummy rowsource.

MySQL is the only major system which lacks a way to generate it.

PostgreSQL implements a special function generate_series to do that, while Oracle and SQL Server can use recursion (CONNECT BY and recursive CTEs, accordingly).

More Related Contents:

  1. Join vs. sub-query
  2. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  3. Natural Sort in MySQL
  4. MySQL get row position in ORDER BY
  5. Subqueries vs joins
  6. MySQL Results as comma separated list
  7. MySQL unknown column in ON clause
  8. MySQL not using indexes with WHERE IN clause?
  9. SQL: Find the max record per group [duplicate]
  10. Group by month and year in MySQL
  11. calculate a sum of type time using sql
  12. MySQL: Transactions vs Locking Tables
  13. MySQL: What is a reverse version of LIKE?
  14. Remove duplicates using only a MySQL query?
  15. Group by date only on a Datetime column
  16. Finding the next available id in MySQL
  17. How exactly does using OR in a MySQL statement differ with/without parentheses?
  18. MySQL: How to copy rows, but change a few fields?
  19. How to renumber primary index
  20. Query that ignore the spaces
  21. MySQL select where column is not empty
  22. Hamming distance on binary strings in SQL
  23. Is it possible to have a default parameter for a mysql stored procedure?
  24. Renaming foreign-key columns in MySQL
  25. How to convert from varbinary to char/varchar in mysql
  26. MySQL GROUP BY behavior
  27. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  28. Update multiple rows with one query?
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment