You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "Abdullahsab3 (via GitHub)" <gi...@apache.org> on 2024/04/30 08:55:40 UTC
[I] Using `date_bin` with a time zone in a time range that contains daylight savings does not work [datafusion]
Abdullahsab3 opened a new issue, #10308:
URL: https://github.com/apache/datafusion/issues/10308
### 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
### 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
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Using `date_bin` with a time zone in a time range that contains daylight savings does not work [datafusion]
Posted by "Abdullahsab3 (via GitHub)" <gi...@apache.org>.
Abdullahsab3 commented on issue #10308:
URL: https://github.com/apache/datafusion/issues/10308#issuecomment-2085174000
I think the wider image of this issue is the fact that the hour at which the daylight savings occur will not be able to get transformed to the desired timezone. For example:
```
select '2019-03-31T02:00:00Z'::timestamp at time zone 'Europe/Brussels';
```
This would also fail for the same reason
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Using `date_bin` with a time zone in a time range that contains daylight savings does not work [datafusion]
Posted by "Abdullahsab3 (via GitHub)" <gi...@apache.org>.
Abdullahsab3 commented on issue #10308:
URL: https://github.com/apache/datafusion/issues/10308#issuecomment-2089994600
I am testing some things out with the `at time zone` operator to understand it better. I think the UTC timestamp casting should be explictly specified first. Some context:
Daylight savings in 2019 is in March 31 at 2AM in local time.
This is `2019-03-31T00:00:00` in UTC I think.
The query `select '2019-03-31T02:00:00Z'::timestamp at time zone 'Europe/Brussels';` seems to be ignoring the `Z` at the end, and it considers the timestamp local time.
Some experiments:
```
select '2019-03-31T02:00:00Z'::timestamp at time zone 'Europe/Brussels';
result> Arrow error: Cast error: Cannot cast timezone to different timezone
```
```
select '2019-03-31T00:00:00'::timestamp at time zone 'UTC' at time zone 'Europe/Brussels'
result> 2019-03-31T01:00:00+01:00
```
```
select '2019-03-31T01:00:00'::timestamp at time zone 'UTC' at time zone 'Europe/Brussels'
result> 2019-03-31T03:00:00+02:00
```
It seems to me that, regardless of the offset provided in the timestamp of the query, the timestamp will considered as a naive date time, and the additional timezone information that is provided in the query will be considered the source of truth.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Using `date_bin` with a time zone in a time range that contains daylight savings does not work [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10308:
URL: https://github.com/apache/datafusion/issues/10308#issuecomment-2089949723
I remember the daylight savings time is quite tricky because it has ambiguous possible time.
See https://github.com/apache/datafusion/issues/8899#issuecomment-1900598499
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Using `date_bin` with a time zone in a time range that contains daylight savings does not work [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #10308:
URL: https://github.com/apache/datafusion/issues/10308#issuecomment-5623281951
Good news on this one: I validated the upstream fix https://github.com/apache/arrow-rs/pull/11038 against your reproducer and it resolves it.
Before, on DataFusion `main` (55.0.0) — note the result column has to be materialized, since wrapping it in `count(*)` lets projection pushdown prune the cast and the error disappears:
```
Arrow error: Cast error: Cannot cast timezone to different timezone
```
After, with arrow-rs#11038 patched in:
```
+---------------------+---------------------------+
| naive | brussels |
+---------------------+---------------------------+
| 2021-03-27T22:30:00 | 2021-03-27T22:30:00+01:00 |
| 2021-03-28T00:30:00 | 2021-03-28T00:30:00+01:00 |
| 2021-03-28T01:30:00 | 2021-03-28T01:30:00+01:00 |
| 2021-03-28T02:30:00 | 2021-03-28T03:30:00+02:00 | <-- nonexistent local time, shifted forward
| 2021-03-28T03:30:00 | 2021-03-28T03:30:00+02:00 |
| 2021-03-28T05:30:00 | 2021-03-28T05:30:00+02:00 |
+---------------------+---------------------------+
```
The offset changes *within* the column, so the resolution is per row rather than one offset applied to the whole array. PostgreSQL 17 and DuckDB/ICU both give `2021-03-28 01:30:00+00` for that nonexistent reading, which matches.
The `date_bin` shape from your original report is also fixed by it, and everything on that path is a `Timestamp(_, None)` -> `Timestamp(_, Some(zone))` cast, so this issue should close once DataFusion picks up an arrow-rs release containing #11038.
For contrast, https://github.com/apache/datafusion/issues/25084 is *not* fully fixed by it — that one also needs https://github.com/apache/arrow-rs/issues/11039, because string literals take a different code path. This issue does not depend on that.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org