findepi commented on issue #12218:
URL: https://github.com/apache/datafusion/issues/12218#issuecomment-2322874281
The SQL spec compliant behavior when casting `timestamp with time zone` to
`timestamp` is to strip time zone information and retain
year/month/day/hour/minute/second fields.
You can observe this behavior in Trino
```
trino> SELECT x "twtz", CAST(x AS timestamp) AS "t" FROM (VALUES
(CAST('2024-08-31 11:47:58.977899 Europe/Warsaw' AS timestamp with time zone)))
t(x);
twtz | t
---------------------------------------+-------------------------
2024-08-31 11:47:58.978 Europe/Warsaw | 2024-08-31 11:47:58.978
(1 row)
```
In a sense PostgreSQL's `timestamp with time zone` to `timestamp` cast
follows the spec because PostgreSQL's `timestamp with time zone` value doesn't
include the time zone information. It's "a point in time in UTC.
```
postgres=# SELECT x "twtz", CAST(x AS timestamp) AS "t" FROM (VALUES
(CAST('2024-08-31 11:47:58.977899 Europe/Warsaw' AS timestamp with time zone)))
t(x);
twtz | t
-------------------------------+----------------------------
2024-08-31 09:47:58.977899+00 | 2024-08-31 09:47:58.977899
```
Now, DF behavior when casting `timestamp with time zone` to `timestamp`
seems to be to convert timestamp with time zone to UTC (retaining point in
time) and then strip zone
```
> SELECT x "twtz", CAST(x AS timestamp) AS "t" FROM (VALUES
(CAST('2024-08-31 11:47:58.977899 Europe/Warsaw' AS timestamp with time zone)))
t(x);
+----------------------------------+----------------------------+
| twtz | t |
+----------------------------------+----------------------------+
| 2024-08-31T11:47:58.977899+02:00 | 2024-08-31T09:47:58.977899 |
+----------------------------------+----------------------------+
```
IMO we should change DF cast from `timestamp with time zone` to `timestamp`
so that it retains year/month/day/hour/minute/second fields and strips the zone
information.
cc @alamb
--
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]
