How to create a list of seconds in MySQL
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开发者_JAVA技巧 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
精彩评论