Group records by time

Question

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)

Answer 1

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.

Answer 2

I have limited SQLite background (and no practice instance), but I'd try grabbing the minutes using

strftime( FORMAT, TIMESTRING, MOD, MOD, ...)

with the %M modifier (http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html)

Then divide that by 15 and get the FLOOR of your quotient to figure out which quarter-hour you're in (e.g., 0, 1, 2, or 3)

cast(x as int)

Getting the floor value of a number in SQLite?

Strung together it might look something like:

Select cast( (strftime( 'YYYY-MM-DD HH:MI:SS', your_time_field, '%M') / 15) as int) from your_table

(you might need to cast before you divide by 15 as well, since strftime probably returns a string)

Then group by the quarter-hour.

Sorry I don't have exact syntax for you, but that approach should enable you to get the functional groupings, after which you can massage the output to make it look how you want.