4

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:

  • (a) gapfill (like timescaledb time_bucket_gapfill, and
  • (b) last value carried forward (locf)

What is the best way to achieve that?

Thanks!

Jonathan
  • 16,077
  • 12
  • 67
  • 106
  • I'm running into the same issue, it's sad to see that Timescale doesn't respond to requests from the community. How did you solve this? – Rob Angelier May 12 '20 at 07:00

1 Answers1

2

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.

therealbene
  • 187
  • 1
  • 6
  • there is now a survey about improving `time_bucket`: https://github.com/timescale/timescaledb/discussions/3258 – TmTron May 21 '21 at 12:12