mhilton commented on issue #10602:
URL: https://github.com/apache/datafusion/issues/10602#issuecomment-5845897142
Seeing as there has been some activity around this recently I've been trying
to think about what reasonable semantics should be for a `date_bin` that
adjusts for DST changes. These are some suggestions designed to promote
discussion rather than dictate policy.
## 1. `date_bin`s should never jump backwards
A naive implementation that simply shifts to the timestamp to local time,
performs the `data_bin` and then shifts back the local time using the timezone
offset can produce surprising results where the bins overlap the discontinuity.
Consider the following times when used with such a naive implementation of
`date_bin(INTERVAL '1 HOUR', t, '1970-01-01T00:30:00Z')`:
```
+----------------------+------------------------+------------------------+--------------------------+
| UTC | Europe/London | date_bin (timestamp) |
date_bin (timestamp UTC) |
+----------------------+------------------------+------------------------+--------------------------+
| 2026-10-24T23:30:00Z | 2026-10-25T00:30:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-24T23:45:00Z | 2026-10-25T00:45:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:00:00Z | 2026-10-25T01:00:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:15:00Z | 2026-10-25T01:15:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:30:00Z | 2026-10-25T01:30:00+01 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T00:45:00Z | 2026-10-25T01:45:00+01 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T01:00:00Z | 2026-10-25T01:00:00+00 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T01:15:00Z | 2026-10-25T01:15:00+00 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T01:30:00Z | 2026-10-25T01:30:00+00 | 2026-10-25T01:30:00+00 |
2026-10-25T01:30:00Z |
| 2026-10-25T01:45:00Z | 2026-10-25T01:30:00+00 | 2026-10-25T01:30:00+00 |
2026-10-25T01:30:00Z |
+----------------------+------------------------+------------------------+--------------------------+
```
As can be seen around the discontinuity it is possible for an absolute
timestamp to be put in a bin earlier than a smaller absolute timestamp. In my
opinion a `date_bin` implementation should be written to avoid this happening.
In this particular case I think it would be better for a DST aware `date_bin`
to follow how UTC would bin the values:
```
+----------------------+------------------------+------------------------+--------------------------+
| UTC | Europe/London | date_bin (timestamp) |
date_bin (timestamp UTC) |
+----------------------+------------------------+------------------------+--------------------------+
| 2026-10-24T23:30:00Z | 2026-10-25T00:30:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-24T23:45:00Z | 2026-10-25T00:45:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:00:00Z | 2026-10-25T01:00:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:15:00Z | 2026-10-25T01:15:00+01 | 2026-10-25T00:30:00+01 |
2026-10-24T23:30:00Z |
| 2026-10-25T00:30:00Z | 2026-10-25T01:30:00+01 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T00:45:00Z | 2026-10-25T01:45:00+01 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T01:00:00Z | 2026-10-25T01:00:00+00 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T01:15:00Z | 2026-10-25T01:15:00+00 | 2026-10-25T01:30:00+01 |
2026-10-25T00:30:00Z |
| 2026-10-25T01:30:00Z | 2026-10-25T01:30:00+00 | 2026-10-25T01:30:00+00 |
2026-10-24T01:30:00Z |
| 2026-10-25T01:45:00Z | 2026-10-25T01:30:00+00 | 2026-10-25T01:30:00+00 |
2026-10-24T01:30:00Z |
+----------------------+------------------------+------------------------+--------------------------+
```
## 2. An `origin_timestamp` parameter without a timezone offset acts as if
it is in the binned timestamp's timezone
Most implementations I can think of would do this for free anyway, but it is
worth stating explicitly. If the `origin_timestamp` does not explicitly say
what time zone it is in then it is treated as if it is in the timezone of the
value being binned. For example when binning timestamps in the
`American/Los_Angeles` timezone then an origin timestamp of
`1970-01-01T00:30:00` works as if it were the absolute value
`1970-01-01T00:30:00-08:00`.
The default `origin_timestamp` should be defined to be `1970-01-01T00:00:00`
in local time, which is the start of the year 1970 in that timezone. So for
`Australia/Adelaide` this will map to `1970-01-01T00:00:00+09:30` or
`1969-12-31T14:30:00Z`.
If the specified `origin_timestamp` is undefined or ambiguous in the binned
timestamp's timezone then I think `date_bin` should output an error.
## 3. A day in a timezone starts at the first instance that reports the date
as being that day
This might seem self-evident, but it is a looser definition than days start
at midnight. This in necessary because historically there have been timezones
where midnight does not exist in local time. For example in `America/Sao_Paulo`
the timestamp `2018-10-03T23:59:59.999999999-03` jumps straight to
`2018-10-04T01:00:00.000000000-02`.
A similar rule needs to apply to the start of months. In the same timezone
`2015-10-31T23:59:59:.999999999-03` jumped straight to
`2015-11-01T01:00:00.000000000-02`.
## 4. The value returned from `date_bin` cannot be after the timestamp being
binned
The timestamp value output from `date_bin` should be lower than or equal to
the timestamp being binned. This is another one that might be obvious but the
arithmetic can get tricky when one starts dealing with negative values.
## 5. Intervals adjusted for daylight savings should not be adjusted further
than the offset change
Where an interval stretches over a discontinuity then the interval may be
grown or shrunk to accommodate the discontinuity. Effort should be made to
avoid an interval's length being further away from requested than is needed for
the daylight savings adjustment.
## 6. Using an `expression` with a timezone is sufficient to opt in to DST
adjustment
I hold this loosely as it might give confusing results for existing
`date_bin` users. However the only alternatives I can think of either restrict
the DST adjustment to intervals specified in months or days, or require an
additional parameter. Seeing as changing timestamp arrays between two defined
timezones is simply a metadata change it seems reasonable that users can cast
to `Timestamp(ns, Some("UTC"))` if they want to avoid DST adjustment.
I haven't actually put much thought into how one would implement all of
this. I'd be interested to hear any other's thoughts.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
