adamhooper commented on issue #686:
URL:
https://github.com/apache/arrow-datafusion/issues/686#issuecomment-884933560
> Theoretically I agree that a two step process is clearest; The challenge
is that the output type of `to_timestamp` needs to be known in advance (aka
planning time) prior to access to the values of the strings.
This sounds exactly like the problems Postgres faced. Postgres' solutions
all have predictable types:
* `SELECT CAST('2021-07-22T9:40:00-04:00' AS TIMESTAMPTZ)` => `2021-07-22
13:40` (zone=UTC)
* `SELECT CAST('2021-07-22T9:40:00-04:00' AS TIMESTAMP)` => `2021-07-22
09:40` (zone=null)
* `SELECT '2021-07-22T13:40Z'::TIMESTAMPTZ AT TIME ZONE 'Europe/Paris'` =>
`2021-07-22 15:40` (zone=null)
* `SELECT '2021-07-22T15:40'::TIMESTAMP AT TIME ZONE 'Europe/Paris'` =>
`2021-07-22 13:40` (zone=UTC)
If this kind of grammar is too tricky to implement -- or, heck, if
DataFusion wants to be more legible -- these solutions could be rewritten in
function-style syntax:
* `TO_TIMESTAMP_UTC()` => input is string, output is Timestamp(UTC). Absence
of offsets in the input string means they're interpreted as UTC. (Postgres
interprets the absence of offsets as session timezone, not UTC; I'm proposing
DataFusion hard-code UTC until it gets session timezone ... which may be never
;).)
* `TO_TIMESTAMP_LOCALTIME()` => input is string, output is Timestamp(null).
Offsets in the input string are ignored.
* `TIMESTAMP_UTC_TO_LOCALTIME(timestamp_utc, zone)` => input is
Timestamp(UTC), output is Timestamp(null).
* `TIMESTAMP_LOCALTIME_TO_UTC(timestamp_localtime, zone)` => input is
Timestamp(null), output is Timestamp(UTC).
Postgres's `TO_TIMESTAMP()` is a quasi-alias for the first item in my lists.
It outputs a fixed type.
--
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]
