duckdb: time_bucket selects wrong bin for day intervals with unaligned origins.

What happens?

when binning using time_bucket and a day interval the bin is incorrect if the origin is not day aligned. verified that postgresql, when using date_bin, produces the expected result.

To Reproduce

both columns should be equal – the tstz is wrong.

set TimeZone='UTC';
select
  time_bucket(interval '1' day, timestamptz '2023-10-07 16:08:09+00', origin) tstz,
  time_bucket(interval '1' day, timestamp   '2023-10-07 16:08:09', origin::timestamp) ts
from
  generate_series(timestamptz '2001-01-03 00:00:00+00',
                  timestamptz '2001-01-03 01:00:00+00',
                  interval '1' hour) as t(origin)
order by origin
;

┌────────────────────────┬─────────────────────┐
│          tstz          │         ts          │
├────────────────────────┼─────────────────────┤
│ 2023-10-07 00:00:00+00 │ 2023-10-07 00:00:00 │
│ 2023-10-06 01:00:00+00 │ 2023-10-07 01:00:00 │
└────────────────────────┴─────────────────────┘

OS:

Ubuntu 22.04.3 LTS x64

DuckDB Version:

v0.9.0

DuckDB Client:

cli

Full Name:

rob m

Affiliation:

unaffiliated

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have