PostgreSQL / TimescaleDB query that group data by custom interval

Question

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)

Answer 1

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