How to group by X minute increments in Presto SQL?

Question

I have data set that looks like the following :

Name, Timestamp, Period, Value
Apple, 2012-03-22 00:00:00.000, 10, 34
Apple, 2012-03-22 00:06:00.000, 10, 23
Orange, 2012-03-22 00:00:00.000, 5, 3
Orange, 2012-03-22 00:08:00.000, 5, 45

Where the column period is the N number of minutes it should be grouped by per hour. So for example Apple should be grouped on 1:10, 1:20, 1:30 ex where Orange is 1:05, 1:10 ect. I would also like to average the Value column over each of these increments.

score 15 · Accepted Answer · answered May 15 '19 at 17:11

With the assumptions that

Period is whole integer and a divisor of 60
for two rows, if Name is the same, Period is the same

the following should do

SELECT
    name,
    date_trunc('minute', timestamp_column)
      - interval '1' minute * (minute(timestamp_column) % period)
      AS timestamp_rounded,
    avg(value)
FROM ...
GROUP BY 1, 2

How to group by X minute increments in Presto SQL?

1 Answers1