Optimize SQLite queries for number of records in time intervals

Question

I have a table which stores a set of events whose schema can be simplified for this example to

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  time INTEGER NOT NULL,
  data BLOB
);
CREATE INDEX by_time ON events(time);

Given a time interval min to max, I want to get the number of events in each 1-hour interval between min and max (concrete example below). The most obvious way to achieve this that I can think of is to compute the required intervals in my code and then for each one run the query

SELECT count(*) FROM events WHERE ? <= time AND time < ?;

Is there a faster way to achieve this by making SQLite deal with splitting the interval into smaller chunks? If this makes the solution simpler, we can assume min and max are exactly at the start/end of an hour interval.

Example

Suppose events contains events with times

100, 200, 1600, 3000,
3800, 4000,
7400,
15000, 15200, 17000,
20400,
22300, 23000

Then I would want a query with min = 3600, max = 21600 to return something like

start |  end  | count
-------------------
 3600 |  7200 | 2
 7200 | 10800 | 1
10800 | 14400 | 0
14400 | 18000 | 3
18000 | 21600 | 1

It doesn't matter exactly what the format of the output is as long as it contains the required counts and a way to identify which interval they refer to.

Answer 1

You can use a recursive CTE to get the time intervals and then LEFT join the table to aggregate:

WITH 
  cte(min, max) AS (SELECT 3600, 21600),
  intervals AS (
    SELECT min from_time, min + 3600 to_time, max
    FROM cte
    WHERE min + 3600 <= max
    UNION ALL
    SELECT to_time, to_time + 3600, max
    FROM intervals
    WHERE to_time + 3600 <= max
  )
SELECT i.from_time, i.to_time,
       COUNT(e.id) count 
FROM intervals i LEFT JOIN events e
ON e.time >= i.from_time AND e.time < i.to_time
GROUP BY i.from_time, i.to_time;

See the demo.