Group records by time
aggregate, group-by, sql, sqlite
Solution
Give this a try:
select datetime((strftime('%s', time) / 900) * 900, 'unixepoch') interval,
count(*) cnt
from t
group by interval
order by interval
Check the fiddle here.
Problem
I have a table containing a datetime column and some misc other columns. The datetime column represents an event happening. It can either contains a time (event happened at that time) or NULL (event didn't happen) I now want to count the number of records happening in specific intervals (15 minutes), but do not know how to do that. example: ``` id | time | foreign_key 1 | 2012-01-01 00:00:01 | 2 2 | 2012-01-01 00:02:01 | 4 3 | 2012-01-01 00:16:00 | 1 4 | 2012-01-01 00:17:00 | 9 5 | 2012-01-01 00:31:00 | 6 ``` I now want to create a query that creates a result set similar to: ``` interval | COUNT(id) 2012-01-01 00:00:00 | 2 2012-01-01 00:15:00 | 2 2012-01-01 00:30:00 | 1 ``` Is this possible in SQL or can anyone advise what other tools I could use? (e.g. exporting the data to a spreadsheet program would not be a problem)