MySQL how to fill missing dates in range?

by

MySQL doesn’t have recursive functionality, so you’re left with using the NUMBERS table trick –

  1. 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;

  2. Populate the table using:

    INSERT INTO `example`.`numbers`
  ( `id` )
VALUES
  ( NULL )

    …for as many values as you need.

  3. 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' ) x

  4. LEFT 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`

More Related Contents:

  1. How to create a MySQL hierarchical recursive query?
  2. Get a list of dates between two dates
  3. How to do the Recursive SELECT query in MySQL?
  4. How to find gaps in sequential numbering in mysql?
  5. Check for x consecutive days – given timestamps in database
  6. MySQL Select last 7 days
  7. How can we find gaps in sequential numbering in MySQL?
  8. Using column alias in WHERE clause of MySQL query produces an error
  9. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  10. Cannot delete or update a parent row: a foreign key constraint fails
  11. Using union and order by clause in mysql
  12. Using an Alias in SQL Calculations
  13. Can a table field contain a hyphen?
  14. How to get difference between two dates in months using MySQL query?
  15. OUTPUT Clause in MySQL
  16. Search all columns of a table using a single where condition with single keyword in mysql
  17. Using ORDER BY and GROUP BY together
  18. Sorting by date & time in descending order?
  19. Case statement in MySQL
  20. MySQL Pivot Table Column Data as Rows
  21. updating columns with a sequence number mysql
  22. MySQL, Concatenate two columns
  23. MySQL: Inner join vs Where [duplicate]
  24. Selecting from two tables with inner join and limit [duplicate]
  25. Why the order of evaluation for expressions involving user variables is undefined?
  26. How to select data where a field has a min value in MySQL?
  27. How to SELECT by MAX(date)?
  28. get a recursive parent list
  29. Deleting a row based on the max value
  30. MySQL Multiple Joins in one query?

Leave a Comment