time_bucket_gapfill is not working when trying to offset the timestamp

[cloud-rocket]

Relevant system information:

• OS: Kubernetes
• PostgreSQL version (output of postgres --version): 10
• TimescaleDB version (output of \dx in psql): 1.4.1
• Installation method: using Docker

Describe the bug
time_bucket_gapfill is not working when trying to offset the timestamp.

To Reproduce

SELECT time_bucket_gapfill('5 minutes', time) + '2.5 minutes'
  AS five_min, last(cpu, time)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;

Expected behavior
The same behavior as with time_bucket (except the gap filling)

Actual behavior

 Could not execute the SQL command.
 Message returned: `ERROR:  no top level time_bucket_gapfill in group by clause

[sukkamehu]

I have the same issue. Query in below

SELECT table.id, to_char(time_bucket_gapfill('15 hours', datetime) at time zone 'utc', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as time,
avg(value) as value, data.description from table JOIN data ON table.id = data.id where table.id in(300,301,302,303,304)
AND datetime BETWEEN '2019-05-01' AND '2019-09-30' group by time, table.id, table.description order by time DESC;

[Krummelz]

It's not a compact workaround and ideally I would want gapfill to take an offset as well, but what I've had to resort to is running gapfill first (and depending on your requirements, using smaller buckets - say 1 hour), then wrap that in a CTE and run a query on the CTE where you do a regular time_bucket of 15 hours offset by your desired amount. Here's an example:

with gapfilled_data as ( 
  select  
    time_bucket_gapfill('1 hour', time, '2019-05-01', '2019-09-30') as hours 
    , count(*) as total 
  from history 
  where time between '2019-05-01' and '2019-09-30' 
) 
select  
  time_bucket('15 hour', hours, '6 hour') as shift 
  , sum(total) as total 
from gapfilled_data  
group by shift 
order by shift

[pantchox]

having the same issue when trying to add + INTERVAL '1 hour' AS hour for example.

[Nizarius]

Any updates on this?

[tiemma]

Experiencing this same issue also

[Haybu]

experiencing the same SQL Error [22023]: ERROR: no top level time_bucket_gapfill in group by clause error when using time_bucket_gapfill function. I wonder if any update. thanks.

[jflambert]

I can't seem to use time_bucket_gapfill at all (timescale 2.4.1), I always get this error

no top level time_bucket_gapfill in group by clause

[Raphyyy]

Same here, time_bucket_gapfill also return this error if you want to format the time column in another time format than default.
For example, formatting in Epoch :

SELECT EXTRACT
	( EPOCH FROM time_bucket_gapfill ( '5 minutes' :: INTERVAL, datetime )) AS time
FROM
	"v1" 
WHERE
	"datetime" > '2022-06-15T21:59:00' 
	AND "datetime" < '2022-06-16T12:00:00' 
GROUP BY
	"time"
ORDER BY
	"time" ASC

ERROR: no top level time_bucket_gapfill in group by clause
I don't have this issue with time_bucket

[genedavis]

@Raphyyy the documentation states that you can't embed time_bucket_gapfill in another function. Though not ideal you can get around it using a CTE, first getting the gapfill values then performing additional transformations afterwards.

[ghost]

Is there a workaround for getting the time buckets to start at the "origin", like with time_bucket()? The examples in @Krummelz's workaround are good for offsetting, but I can't see how to adapt them to align my first time bucket to start at (e.g.) start_time..

[melicheradam]

+1