[Feature]: Allow bucketing on previous values

[jflambert]

What problem does the new feature solve?

As requested by @erimatnor, I am converting issue #6528 into a feature request. It's possible this overlaps with the various other feature requests that concern time_bucket_gapfill and the missing offset parameter.

Request: When using time_bucket_gapfill along with locf and last, I want each bucket to contain the value of the previous bucket, rather than the ongoing bucket. Concretely, I want to report on the actual value at a given timestamp, rather than what it will be at the end of said timestamp.

create table test
(
  ts TIMESTAMPTZ,
  val INTEGER
);
select create_hypertable('test', 'ts');

delete from test;
INSERT INTO TEST (ts, val) VALUES
('2023-01-01 00:00:00', 0),
('2023-01-01 00:00:30', 0),
('2023-01-01 00:01:00', 1),
('2023-01-01 00:02:00', 2),
('2023-01-01 00:03:00', 3), -- expecting 3 for third minute
('2023-01-01 00:03:59', 4), -- expecting 4 for fourth minute
('2023-01-01 00:05:00', 5);

--explain analyze
SELECT time_bucket_gapfill('1 minute', ts) AS ts_fill, locf(last(val, ts), treat_null_as_missing=>true)
FROM test
WHERE ts BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 00:07:00'
group by ts_fill
order by ts_fill ASC;

Current Result:

Desired Result:

Alternative (does not include gapfilling):

CREATE FUNCTION IMM_CEIL_TIMESTAMP(TIMESTAMPTZ) RETURNS TIMESTAMPTZ AS $$
  SELECT COALESCE(NULLIF(DATE_TRUNC('MINUTE', $1), $1) + '1 MINUTE'::INTERVAL, $1);
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;

select distinct on (ts_ceil) IMM_CEIL_TIMESTAMP(ts) AS ts_ceil, val
from test
order by ts_ceil asc, ts desc;

What does the feature do?

Allows me to "downsample" numerous readings in a precise manner (no averages, min/max, etc). Simply reports the observed, or carried value at a given timestamp.

Implementation challenges

May be a duplicate of #1304 and #3212

I may even be asking for something which could require a new function. This is basically a date_trunc with extra logic to carry the value.