Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)

by

You can generate a table of “buckets” by adding intervals created by generate_series(). This SQL statement will generate a table of five-minute buckets for the first day (the value of min(measured_at)) in your data.

select 
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, (24*60), 5) n

Wrap that statement in a common table expression, and you can join and group on it as if it were a base table.

with five_min_intervals as (
  select 
    (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
    (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
  from generate_series(0, (24*60), 5) n
)
select f.start_time, f.end_time, avg(m.val) avg_val 
from measurements m
right join five_min_intervals f 
        on m.measured_at >= f.start_time and m.measured_at < f.end_time
group by f.start_time, f.end_time
order by f.start_time

Grouping by an arbitrary number of seconds is similar–use date_trunc().

A more general use of generate_series() lets you avoid guessing the upper limit for five-minute buckets. In practice, you’d probably build this as a view or a function. You might get better performance from a base table.

select 
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, ((select max(measured_at)::date - min(measured_at)::date from measurements) + 1)*24*60, 5) n;

More Related Contents:

  1. Insert, on duplicate update in PostgreSQL?
  2. How does the search_path influence identifier resolution and the “current schema”
  3. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  4. How do you find the row count for all your tables in Postgres
  5. Run batch file with psql command without password
  6. invalid byte sequence for encoding “UTF8”
  7. Postgresql: Conditionally unique constraint
  8. See and clear Postgres caches/buffers?
  9. “psql: could not connect to server: Connection refused” Error when connecting to remote database
  10. ClassNotFoundException with PostgreSQL and JDBC
  11. How to get function parameter lists (so I can drop a function)
  12. Find difference between timestamps in seconds in PostgreSQL
  13. How to convert a postgres database to sqlite
  14. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  15. Error when setting n_distinct using a plpgsql variable
  16. How can I change a PostgreSQL user password?
  17. I forgot the password I entered during PostgreSQL installation
  18. Function to loop through and select data from multiple tables
  19. SQL query column does not exist error
  20. Switch role after connecting to database
  21. SQL Sub queries in check constraint
  22. Postgresql enforce unique two-way combination of columns
  23. When do Postgres column or table names need quotes and when don’t they?
  24. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  25. how to store PostgreSQL jsonb using SpringBoot + JPA?
  26. PostgreSQL how to concat interval value ‘2 days’
  27. Displaying image in Ireports using PostgreSql
  28. Orbeon Forms Postgres DB connection
  29. Spring Docker container cannot access Postgres Docker container
  30. PostgreSQL composite primary key

Leave a Comment