Use time_bucket_gapfill, the return result exceeds the start time of the limit

XXM 0
Viewed 28
SELECT time_bucket_gapfill(86400000, create_time) as "bucket", count(*)
FROM annotation
where create_time > 1661961600000 and create_time < 1664467200000
GROUP BY bucket

The return result is

1661904000000   
1661990400000   1
1662076800000   
1662163200000   
1662249600000   
1662336000000   
1662422400000   
1662508800000   4
1662595200000   
1662681600000

You can see that what I limit is to start from 1661961600000 and end at 1664467200000, and the result of the first line is 1661904000000，smaller than my limited start time

timescaledb database postgresql
1 Answers

time_bucket and time_bucket_gapfill both return the smallest value in the bucket that a value falls in. So if you look at the output of SELECT time_bucket(86400000, 1661961600000); you'll see that it's 1661904000000. So the smallest value could be as far away from an input value as bucket_width. So unless your value in the where clause is exactly at a bucket edge (which you could do by doing WHERE create_time > time_bucket(86400000, 1661961600000) ) then you will see values that are smaller than the value in your WHERE clause because the output will be that lowest value in the bucket.

Another option if you want a bucket that starts at your minimum restricted value you can consider using the offset parameter of time_bucket.

davidk0
Related