Best way to count rows by arbitrary time intervals

by

Luckily, you are using PostgreSQL. The window function generate_series() is your friend.

Test case

Given the following test table (which you should have provided):

CREATE TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp '2018-05-01'
                     , timestamp '2018-05-08'
                     , interval '7 min') + random() * interval '7 min';

One event for every 7 minutes (plus 0 to 7 minutes, randomly).

Basic solution

This query counts events for any arbitrary time interval. 17 minutes in the example:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

The query retrieves minimum and maximum ts from the base table to cover the complete time range. You can use an arbitrary time range instead.

Provide any time interval as needed.

Produces one row for every time slot. If no event happened during that interval, the count is 0.

Be sure to handle upper and lower bound correctly. See:

The window function lead() has an often overlooked feature: it can provide a default for when no leading row exists. Providing 'infinity' in the example. Else the last interval would be cut off with an upper bound NULL.

Minimal equivalent

The above query uses a CTE and lead() and verbose syntax. Elegant and maybe easier to understand, but a bit more expensive. Here is a shorter, faster, minimal version:

SELECT start_time, count(e.ts) AS events
FROM  (SELECT generate_series(min(ts), max(ts), interval '17 min') FROM event) g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '17 min'
GROUP  BY 1
ORDER  BY 1;

Example for “every 15 minutes in the past week”`

Formatted with to_char().

SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '15 min'
GROUP  BY start_time
ORDER  BY start_time;

Still ORDER BY and GROUP BY on the underlying timestamp value, not on the formatted string. That’s faster and more reliable.

db<>fiddle here

Related answer producing a running count over the time frame:

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Two SQL LEFT JOINS produce incorrect result
  4. PostgreSQL GROUP BY different from MySQL?
  5. Create a pivot table with PostgreSQL
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  8. Return multiple columns of the same row as JSON array of objects
  9. How to include “zero” / “0” results in COUNT aggregate?
  10. GROUP BY + CASE statement
  11. Selecting data into a Postgres array
  12. Compute percents from SUM() in the same SELECT sql query
  13. Get the distinct sum of a joined table column
  14. Perform this hours of operation query in PostgreSQL
  15. Aggregate a single column in query with many columns
  16. Multiple array_agg() calls in a single query
  17. Postgres window function and group by exception
  18. Calculate working hours between 2 dates in PostgreSQL
  19. PostgreSQL where all in array
  20. SQL where joined set must contain all values but may contain more
  21. Find records where join doesn’t exist
  22. Rails 4 LIKE query – ActiveRecord adds quotes
  23. Count cumulative total in Postgresql
  24. Prepared Statement on Postgresql in Rails
  25. How to perform a select query in a DO block?
  26. Join a count query on generate_series() and retrieve Null values as ‘0’
  27. Truncate timestamp to arbitrary intervals
  28. SQL update fields of one table from fields of another one
  29. Table or column name cannot start with numeric?
  30. PostgreSQL column ‘foo’ does not exist

Leave a Comment