Using `date_bin` with a time zone in a time range that contains daylight savings does not work
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### Describe the bug
When using the `date_bin` function, while providing a time zone to the column used in `date_bin` , and using a range that contains daylight savings (i.e. part of the range would be for example with an offset of `+01:00`, and another part with an offset of `+02:00`), the querying fails with the following error:
```
rpc error: code = InvalidArgument desc = External error: Arrow error: Cast error: Cannot cast timezone to different timezone
```
The query that I used is:
```
select
date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
from
raw_data
where
time >= '2021-03-27T22:00:00.000Z'
and
time <= '2021-03-29T00:00:00.000Z'
group by date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
```
Notice that the interval used `[2021-03-27T22:00:00.000Z, 2021-03-29T00:00:00.000Z]` contains daylight savings within the range, meaning that in this case part of the range will be with an offset of `+01:00` (before the daylight savings), and another part will be with an offset of `+02:00` (after the daylight savings)
~~I suspect the issue is that Arrow is attempting to case the times with a 2 hours offset to the times of an 1 hour offset~~
**Update:**
The issue is the daylight savings hour. This query works fine:
```
select
date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
from
raw_data
where
time >= '2021-03-27T22:00:00.000Z'
and
time <= '2021-03-29T00:00:00.000Z'
-- exclude the daylight savings hour
and time not between '2021-03-28T02:00:00Z' and '2021-03-28T03:00:00Z'
group by date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
```
In the query above, we exclude the daylight savings hour of that year for that timezone.
### To Reproduce
Execute the following query
```
select
date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
from
raw_data
where
time >= '2021-03-27T22:00:00.000Z'
and
time <= '2021-03-29T00:00:00.000Z'
group by date_bin(interval '1 hour', time at time zone 'Europe/Brussels')
```
### Expected behavior
I should get the data aggregated by time. Part of the interval will be with a 2 hours offset, another part will be with a 1 hour offset
### Additional context
`raw_data` is a table that contains values for each 15 minutes. The table with its data is stored in influxDB 3.0 that uses the FDAP stack
_No response_
Contributor guide
Research direction
Begin at the date_bin SQL function and the timezone conversion path implicated by the Arrow cast error. Reproduce the supplied query across the Europe/Brussels daylight-saving transition, then add regression coverage for the full range. Done means the query succeeds and aggregates timestamps with both offsets without requiring the daylight-saving hour to be excluded.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100