Problem Statement
Consider an Events table containing time-stamped events. The objective is to report the count of events occurring within specific time intervals, such as days, hours, or user-defined intervals. The goal is to determine the most efficient approach for dynamically generating this information through a single SQL query in Postgres.
Solution
Using Date_bin() (Postgres 14 or Newer)
Postgres 14 introduces the 'date_bin()' function, providing a straightforward solution to this problem:
SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time, count(e.ts) AS events FROM event e GROUP BY 1 ORDER BY 1;
Generating a Full Set of Rows (Postgres 13 or Older)
For earlier versions of Postgres, the following query generates a full set of time slots and performs a LEFT JOIN to count events within each interval:
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;
Considerations
The above is the detailed content of How to Efficiently Count Events by Time Intervals in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!