I have the following query:
SELECT timestamp,
COUNT(*)
FROM table
GROUP BY timestamp
But some timestamps do not show up because there is no data. Here's an example
1:00:00 | 3
1:00:02 | 17
1:00:03 | 2
Notice that 1:00:01 is missing. Is there a way to make the 1:00:01 | 0
appear in the result?
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 NUMBERS
(id)
VALUES
(NULL)
...for as many values as you need.
Use DATE_ADD to construct a list of times, increasing the seconds based on the NUMBERS.id value:
SELECT x.*
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T')
FROM numbers n) x
LEFT JOIN onto your table of data based on the time portion:
SELECT x.ts AS timestamp,
COALESCE(COUNT(y.timestamp), 0) AS cnt
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 SECOND), '%T') AS ts
FROM numbers n) x
LEFT JOIN TABLE y ON y.timestamp = x.ts
GROUP BY x.ts