Abdullahsab3 commented on issue #10602:
URL: https://github.com/apache/datafusion/issues/10602#issuecomment-2150564966
Another thing that needs to be taken into consideration: I think the
returned timestamp from `date_bin` needs to be in the timezone that was
provided as an argument. For example:
The returned timestamp is in correct local time, but the underlying Arrow
kernel has no timezone information about it (which I think means that the
timestamp will be interpreted as being in UTC)
```sql
❯ select to_char('2024-04-30T02:00:00' at time zone 'UTC' at time zone
'Europe/Brussels', '%F %X')::timestamp;
+----------------------------------------------------+
| to_char(Utf8("2024-04-30T02:00:00"),Utf8("%F %X")) |
+----------------------------------------------------+
| 2024-04-30T04:00:00 |
+----------------------------------------------------+
1 row in set. Query took 0.002 seconds.
❯ select arrow_typeof(to_char('2024-04-30T02:00:00' at time zone 'UTC' at
time zone 'Europe/Brussels', '%F %X')::timestamp);
+------------------------------------------------------------------+
| arrow_typeof(to_char(Utf8("2024-04-30T02:00:00"),Utf8("%F %X"))) |
+------------------------------------------------------------------+
| Timestamp(Nanosecond, None) |
+------------------------------------------------------------------+
1 row in set. Query took 0.003 seconds.
```
When we add the timezone information:
```sql
❯ select to_char('2024-04-30T02:00:00' at time zone 'UTC' at time zone
'Europe/Brussels', '%F %X') at time zone 'Europe/Brussels';
+----------------------------------------------------+
| to_char(Utf8("2024-04-30T02:00:00"),Utf8("%F %X")) |
+----------------------------------------------------+
| 2024-04-30T04:00:00+02:00 |
+----------------------------------------------------+
1 row in set. Query took 0.001 seconds.
❯ select arrow_typeof(to_char('2024-04-30T02:00:00' at time zone 'UTC' at
time zone 'Europe/Brussels', '%F %X') at time zone 'Europe/Brussels');
+------------------------------------------------------------------+
| arrow_typeof(to_char(Utf8("2024-04-30T02:00:00"),Utf8("%F %X"))) |
+------------------------------------------------------------------+
| Timestamp(Nanosecond, Some("Europe/Brussels")) |
+------------------------------------------------------------------+
1 row in set. Query took 0.001 seconds.
```
The timezone information there is important, as the timestamp will be
otherwise interpreted by external integrations (such as Grafana) as being in
UTC.
Note that applying `at time zone 'Europe/Brussels'` will never fail during
daylight savings hours, as the returned value from `to_char` is always in
localtime, meaning that the 'missing/additional hour' should not be an issue
for the `at time zone` operator.
--
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]
