How to query max and min records of every 5 seconds (or any other user-defined period) within a selected period of time in postgres

Question

I have a postgres 9.6 table with hundreds of millions of records in 'prices' table with only four columns: uid, price, unit, dt. dt is a datetime in standard format like '2017-05-01 00:00:00.585' with fractions of a second. It might be none to dozens record each second.

I can find MAX and MIN price record in some time period. I can quite easily select a period using

SELECT date_trunc('second', dt) as time, min(price), max(price)
FROM prices
WHERE dt >= '2017-05-01 00:00:00' AND dt < '2017-05-01 00:00:59'
GROUP BY time
ORDER BY time;

But date_trunc does not have flexibility and does not allow to set arbitrary period, for example 5 seconds, or 10 minutes. Is there a way to solve it?

Answer 1

Use generate_series to get the ranges on the interval of time you need to search. Then use dd + '5 seconds'::interval to get the upper bound of the range

In this example we look for one day of data every 5 seconds

WITH ranges as (
    SELECT dd as start_range, 
           dd + '5 seconds'::interval as end_range, 
           ROW_NUMBER() over () as grp
    FROM generate_series
            ( '2017-05-01 00:00:00'::timestamp 
            , '2017-05-02 00:00:00'::timestamp
            , '5 seconds'::interval) dd
), create_grp as (
    SELECT r.grp, r.start_range, r.end_range, p.price
    FROM prices p
    JOIN ranges r
      ON p.date >= r.start_range
     AND p.date < r.end_range
)        
SELECT grp, start_range, end_range, MIN(price), MAX(price)
FROM create_grp
GROUP BY grp, start_range, end_range
ORDER BY grp