apache / apache/datafusion

Using `date_bin` with a time zone in a time range that contains daylight savings does not work

Open
#10,308 4 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.