Time weighted average is null for time buckets with one occurrence

[ap5d]

trafficstars

Relevant system information:

• OS: Ubuntu Server 21.04
• PostgreSQL version (output of SELECT version();): PostgreSQL 13.4 (Ubuntu 13.4-1.pgdg21.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1) 10.3.0, 64-bit
• TimescaleDB Toolkit version (output of \dx timescaledb_toolkit in psql): 1.2
• Installation method: docker - timescale/timescaledb-ha:pg13.4-ts2.4.2-latest

Describe the bug Time weighted average is null for each time bucket that contains only one row.

To Reproduce Create test data:

DROP TABLE IF EXISTS measurements;
CREATE TABLE IF NOT EXISTS measurements
(
    time timestamp with time zone NOT NULL,
    temperature double precision
);
SELECT create_hypertable('measurements', 'time');

INSERT INTO measurements(time, temperature) VALUES
('2021-09-24 22:00:09', 22.5),
('2021-09-24 22:00:19', 22.5),
('2021-09-24 22:00:28', 22.5),
('2021-09-24 22:00:40', 22.5),
('2021-09-24 22:00:49', 22.5),
('2021-09-24 22:00:59', 22.5);

Try to obtain TWA's using a small bucket size of 10 s:

SELECT time_bucket('10s'::interval, time) AS time,
	average(time_weight('linear', time, temperature)) AS temperature
FROM measurements
GROUP BY 1
ORDER BY 1;

Expected behavior I would expect the query above to return

"2021-09-24 22:00:00+00"	22.5
"2021-09-24 22:00:10+00"	22.5
"2021-09-24 22:00:20+00"	22.5
"2021-09-24 22:00:40+00"	22.5
"2021-09-24 22:00:50+00"	22.5

Actual behavior The query returns instead

"2021-09-24 22:00:00+00"	[null]
"2021-09-24 22:00:10+00"	[null]
"2021-09-24 22:00:20+00"	[null]
"2021-09-24 22:00:40+00"	22.5
"2021-09-24 22:00:50+00"	[null]

Additional context Using a 20 s bucket size also produces a null average for this data

"2021-09-24 22:00:00+00"	22.5
"2021-09-24 22:00:20+00"	[null]
"2021-09-24 22:00:40+00"	22.5

[davidkohn88]

Unfortunately, I don't think this is a bug, if there is one occurrence without bounds the time weighted average has to be null, the way it is calculated you need a last value. So I think this is really a feature request for two things: 1) a with_bounds type function for TWA that will, at least with LOCF project forward to the end of the range even if there's only one point and 2) a window function that will do lookahead lookback so TWA can see things outside of the bucket it's working in. So, I guess I'd reclassify as a limitation or something we are looking into how to make this work better!