time_bucket() origin argument on time_bucket_gapfill()

[alberto-pelosi]

Hi,

I would need an optional argument like time_bucket() origin, on time_bucket_gapfill().
Is there a way to gain the same effect?

In my use case, I need to split the data in bucket of eight hours starting from a specific timestamp (i.e. the start time of a workshift).

It would be nice to have origin also on time_bucket_gapfill().

Thanks

Alberto

[atanasovskib]

Can the start/end optional parameters help here? (https://docs.timescale.com/latest/api#time_bucket_gapfill-optional-arguments) You can set them to the start of your 8 hour workshift

[alberto-pelosi]

It seems that start parameter has different semantic.
I have tried to use start and finish, es:

select public.time_bucket_gapfill('8 hours' , telemetry.time , start => '2019-11-25 06:00', finish => '2019-11-26 14:00') as t, count(*) from telemetry group by t

But the first bucket starts from 2019-11-25 01:00:00

I need to start from 2019-11-25 06:00:00

[rubengees]

To have the same effect as time_bucket_gapfill, but with origin we used the normal time_bucket routine and filled up the gaps ourselves. Something like this could work:

select COALESCE(t1.t, t2.t) AS t from(
select time_bucket('8 hours' , telemetry.time , '2019-11-25 06:00', '2019-11-26 14:00') as t, count(*) from telemetry group by t) t1
right join (select t as t from generate_series('2019-11-25 06:00', '2019-11-26 14:00', interval '8 hours') t) t2
on t1.t = t2.t

Not actually tried the query, but hopefully you get the idea :)

The idea is to generate another table in our time range and join it with the bucket table.

[alberto-pelosi]

Thank you @rubengees

[svenklemm]

Duplicate of #1304