time_bucket_gapfill (TimescaleDB) : how to shift or set the start of the gapfill periods?

Viewed 2105

(I am using PostgreSQL 11.7-2 and TimescaleDB 1.6.1 with Ubuntu Server 18.04.4)

I am aggregating data with gaps with the time_bucket_gapfill function but contrary to the time_bucket function I can't find a way to set an offset or origin to the time bucket intervals. Here's an example:

Let's say I have this table:

DROP TABLE IF EXISTS demo;
CREATE TABLE demo
(
    t   TIMESTAMP WITH TIME ZONE NOT NULL,
    val REAL
);
SELECT create_hypertable('demo', 't');


INSERT INTO demo(t, val) VALUES
('2020-05-28T11:00:40', 1),
('2020-05-28T11:01:45', 5),
('2020-05-28T11:02:10', 15),
('2020-05-28T11:03:35', 30)
;

I would like to aggregate data every minute but starting from 11:00:30, so here there will be a gap between 2:30 and 3:30 which value will be interpolated from previous and next values like this:

0 -------------

30  - - - - - - - - - - <<<<<
            1
1' -------------               average : 1

1'30 - - - - - - - - - -<<<<<
            5
2'00 -----------               average : 10
            15
2'30 - - - - - - - - - -<<<<<

3'00 -----------               average (interpolated) : 20

3'30 - - - - - - - - - -<<<<<
            30
4'00 -----------              average : 30

4'30 - - - - - - - - - -<<<<<

But when I run the following commands, the aggregation starts from 0 seconds, not from 30 seconds, and they do not return the expected previous average values:

SELECT time_bucket_gapfill('1 minutes', t,
start=>'2020-05-28T11:00:30',finish=>'2020-05-28T11:04:00') AS dtime,
avg(val) AS valavg, interpolate(avg(val))
FROM demo
GROUP BY dtime
ORDER BY dtime ASC;

returns

         dtime          | valavg | interpolate 
------------------------+--------+-------------
 2020-05-28 11:00:00+02 |      1 |           1
 2020-05-28 11:01:00+02 |      5 |           5
 2020-05-28 11:02:00+02 |     15 |          15
 2020-05-28 11:03:00+02 |     30 |          30
(4 rows)

and

SELECT time_bucket_gapfill('1 minutes', t) AS dtime,
avg(val) AS valavg, interpolate(avg(val))
FROM demo
WHERE t BETWEEN '2020-05-28T11:00:30' AND '2020-05-28T11:04:00'
GROUP BY dtime
ORDER BY dtime ASC;

returns

         dtime          | valavg | interpolate 
------------------------+--------+-------------
 2020-05-28 11:00:00+02 |      1 |           1
 2020-05-28 11:01:00+02 |      5 |           5
 2020-05-28 11:02:00+02 |     15 |          15
 2020-05-28 11:03:00+02 |     30 |          30
 2020-05-28 11:04:00+02 |        |            
(5 rows)

How can I shift the origin of the time bucket intervals with time_bucket_gapfill ?

0 Answers
Related