date_bin_table() treats exclusive time range boundaries as inclusive

[brianpursley]

date_bin_table() always treats the time range as an inclusive range [], even if you specify exclusive boundaries ().

It looks like this is because it uses lower() and upper() to extract the range bounds and then uses them with a between operator, instead of the tstzrange containment operator.

My setup and test below:

CREATE TABLE ts_example (
    timestamp TIMESTAMPTZ NOT NULL,
    location_id INT NOT NULL,
    value DECIMAL(7, 2) NOT NULL
) PARTITION BY RANGE (timestamp);

SELECT enable_ts_table('ts_example');

INSERT INTO ts_example (timestamp, location_id, value) VALUES
('2024-01-01 00:00:00+00', 1, 1),
('2024-01-01 00:30:00+00', 1, 2),
('2024-01-01 01:00:00+00', 1, 3),
('2024-01-01 01:30:00+00', 1, 4),
('2024-01-01 02:00:00+00', 1, 5),
('2024-01-01 02:30:00+00', 1, 6),
('2024-01-01 03:00:00+00', 1, 7),
('2024-01-01 03:30:00+00', 1, 8),
('2024-01-01 04:00:00+00', 1, 9),
('2024-01-01 04:30:00+00', 1, 10);

postgres=# select * from ts_example;
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 00:00:00+00 |           1 |  1.00
 2024-01-01 00:30:00+00 |           1 |  2.00
 2024-01-01 01:00:00+00 |           1 |  3.00
 2024-01-01 01:30:00+00 |           1 |  4.00
 2024-01-01 02:00:00+00 |           1 |  5.00
 2024-01-01 02:30:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:30:00+00 |           1 |  8.00
 2024-01-01 04:00:00+00 |           1 |  9.00
 2024-01-01 04:30:00+00 |           1 | 10.00
(10 rows)

postgres=# select * from ts_example where timestamp <@ '[2024-01-01 02:00, 2024-01-01 04:00]'::tstzrange;
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 02:00:00+00 |           1 |  5.00
 2024-01-01 02:30:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:30:00+00 |           1 |  8.00
 2024-01-01 04:00:00+00 |           1 |  9.00
(5 rows)

postgres=# select * from ts_example where timestamp <@ '(2024-01-01 02:00, 2024-01-01 04:00)'::tstzrange;
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 02:30:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:30:00+00 |           1 |  8.00
(3 rows)

postgres=# select * from date_bin_table(NULL::ts_example, '1 hour', '[2024-01-01 02:00, 2024-01-01 04:00]');
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 02:00:00+00 |           1 |  5.00
 2024-01-01 02:00:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:00:00+00 |           1 |  8.00
 2024-01-01 04:00:00+00 |           1 |  9.00
(5 rows)

postgres=# select * from date_bin_table(NULL::ts_example, '1 hour', '(2024-01-01 02:00, 2024-01-01 04:00)');
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 02:00:00+00 |           1 |  5.00
 2024-01-01 02:00:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:00:00+00 |           1 |  8.00
 2024-01-01 04:00:00+00 |           1 |  9.00
(5 rows)

For the output of the final query, I was expecting to see this:

postgres=# select * from date_bin_table(NULL::ts_example, '1 hour', '(2024-01-01 02:00, 2024-01-01 04:00)');
       timestamp        | location_id | value 
------------------------+-------------+-------
 2024-01-01 02:00:00+00 |           1 |  6.00
 2024-01-01 03:00:00+00 |           1 |  7.00
 2024-01-01 03:00:00+00 |           1 |  8.00
(3 rows)

[jasonmp85]

Wow, this is a nicely written ticket! Thanks for the detailed repro cases and explanation of expected behavior. You're right, this is an oversight on my part and I'll need to rewrite a little of this to correct the behavior. I'll update here once I have.