开发者

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 -

  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 NUMBERS
      (id)
    VALUES
      (NULL)
    

    ...for as many values as you need.

  3. 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
    
  4. 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
    
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜