CODEWITHSUNDEEP
postgresqltypeormtimescaledb
Jonathan
Jonathan

Reputation: 16349

how to time_bucket by month with timescaleDB

As of this question, I know there is an open feature request to add month/year to the time_bucket function.

My question is, what is the best way to accomplish this now. This issue mentions date_trunc

Here are the two approaches:

timescaledb's time_bucket

SELECT time_bucket('1 week', timestamp) AS "one_week", 
       count(*) AS "count", 
       first(value, timestamp) AS "first", 
       last(value, timestamp) AS "last" 
FROM "event" "event" 
WHERE event."signalId" = $1 
GROUP BY one_week 
ORDER BY one_week DESC

postgres date_trunc

SELECT date_trunc('month', timestamp) AS "one_month", 
       count(*) AS "count", 
       first(value, timestamp) AS "first", 
       last(value, timestamp) AS "last" 
FROM "event" "event" 
WHERE event."signalId" = $1 
GROUP BY one_month 
ORDER BY one_month DESC

Both of those work as expected (though I have not done any performance testing).

I would like to acheive:

What is the best way to achieve that?

Thanks!

Upvotes: 4

Views: 4648

Answers (1)

therealbene
therealbene

Reputation: 367

Looks like they are planning to support this in the future, but it has been not been included in planning for any milestones, check https://github.com/timescale/timescaledb/issues/414

For now, I do a workaround using time_bucket('1 day', timestamp) or include it as part of CTE / with, from which I will call the on date_trunc('month', time_bucketed_day_column). This way, timescaledb's gapfill function from smaller interfal (day) should be carried on the longer time interval.

Upvotes: 2

Related Questions