(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 ?