mysql 30秒,SELECT / GROUP BY - 时间段（10秒，30秒等）

I have a table (MySQL) that captures samples every n seconds. The table has many columns, but all that matters for this is two: a time stamp (of type TIMESTAMP) and a count (of type INT).

What I would like to do, is get sums and averages of the count column over a range of times. For instance, I have samples every 2 seconds recorded, but I would like the sum of the count column for all the samples in a 10 second or 30 second window for all samples.

Here's an example of the data:

+---------------------+-----------------+

| time_stamp | count |

+---------------------+-----------------+

| 2010-06-15 23:35:28 | 1 |

| 2010-06-15 23:35:30 | 1 |

| 2010-06-15 23:35:30 | 1 |

| 2010-06-15 23:35:30 | 942 |

| 2010-06-15 23:35:30 | 180 |

| 2010-06-15 23:35:30 | 4 |

| 2010-06-15 23:35:30 | 52 |

| 2010-06-15 23:35:30 | 12 |

| 2010-06-15 23:35:30 | 1 |

| 2010-06-15 23:35:30 | 1 |

| 2010-06-15 23:35:33 | 1468 |

| 2010-06-15 23:35:33 | 247 |

| 2010-06-15 23:35:33 | 1 |

| 2010-06-15 23:35:33 | 81 |

| 2010-06-15 23:35:33 | 16 |

| 2010-06-15 23:35:35 | 1828 |

| 2010-06-15 23:35:35 | 214 |

| 2010-06-15 23:35:35 | 75 |

| 2010-06-15 23:35:35 | 8 |

| 2010-06-15 23:35:37 | 1799 |

| 2010-06-15 23:35:37 | 24 |

| 2010-06-15 23:35:37 | 11 |

| 2010-06-15 23:35:37 | 2 |

| 2010-06-15 23:35:40 | 575 |

| 2010-06-15 23:35:40 | 1 |

| 2010-06-17 10:39:35 | 2 |

| 2010-06-17 10:39:35 | 2 |

| 2010-06-17 10:39:35 | 1 |

| 2010-06-17 10:39:35 | 2 |

| 2010-06-17 10:39:35 | 1 |

| 2010-06-17 10:39:40 | 35 |

| 2010-06-17 10:39:40 | 19 |

| 2010-06-17 10:39:40 | 37 |

| 2010-06-17 10:39:42 | 64 |

| 2010-06-17 10:39:42 | 3 |

| 2010-06-17 10:39:42 | 31 |

| 2010-06-17 10:39:42 | 7 |

| 2010-06-17 10:39:42 | 246 |

+---------------------+-----------------+

The output I would like (based on the data above) should look like this:

+---------------------+-----------------+

| 2010-06-15 23:35:00 | 1 | # This is the sum for the 00 - 30 seconds range

| 2010-06-15 23:35:30 | 7544 | # This is the sum for the 30 - 60 seconds range

| 2010-06-17 10:39:35 | 450 | # This is the sum for the 30 - 60 seconds range

+---------------------+-----------------+

I have used GROUP BY to gather these numbers by the second, or by the minute, but I can't seem to figure out the syntax to get the sub-minute or range of seconds GROUP BY commands to work correctly.

I am mostly going to be using this query to syphon data from this table to another table.

Thanks!

解决方案

GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 30

or say for some reason you wanted to group them in 20-second intervals it would be DIV 20 etc. To change the boundaries between GROUP BY values you could use

GROUP BY (UNIX_TIMESTAMP(time_stamp) + r) DIV 30

where r is a literal nonnegative integer less than 30. So

GROUP BY (UNIX_TIMESTAMP(time_stamp) + 5) DIV 30

should give you sums between hh:mm:05 and hh:mm:35 and between hh:mm:35 and hh:mm+1:05.