1

I have a working query that allows me to sum value field each day.

SELECT sum(value) as sum_value, to_char(time::date,'DD-MM-YY') as day 
FROM "measures"  
GROUP BY "day" 
ORDER BY "day" asc

Is it possible to do the same query, but instead of grouping by day, grouping it by 2 days,or a specific duration ( days only, not hours)

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Juliatzin
  • 18,455
  • 40
  • 166
  • 325

1 Answers1

0

You can use timescaledb extension time_bucket() function as @Mike Organek commented or use native PosgreSQL data transformation like:

SELECT 
    sum(value) as sum_value, 
    to_char(time::date,'DD-MM-YY') as day,
    floor(extract(epoch from "time")/(60*60*24*2)) as time_bucket
FROM "measures"  
GROUP BY "time_bucket" 
ORDER BY "day" asc;

In above query we extract unixtime from datetime column, divide it to grouping period in seconds (60*60*24*2 - mean 2 days or 48 hours) and round it fro use into group by statement

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
  • In general, would recommend using time_bucket when possible. It does what you want, and some planning optimizations and other capabilities will work well with `time_bucket` but not some of the other approaches as detailed. (Timescale person here) – Mike Freedman Aug 04 '20 at 00:06