[GitHub] [arrow-datafusion] adamhooper edited a comment on issue #686: Specific timezone support for `to_timetamp*()`

[GitBox <gi...@apache.org>]

adamhooper edited a comment on issue #686:
URL: https://github.com/apache/arrow-datafusion/issues/686#issuecomment-884485767


   > to_timestamp('1970-01-01', 'UTC')	`Timestamp(Nanoseconds, Utc)`	5*3600000000000 (5 hour of nanoseconds) **assuming code was run on a computer set to EDT, UTC-5:00**
   
   Oh, I see -- this is existing behavior. But then, does existing behavior define a different result for row 1? https://github.com/apache/arrow-datafusion/blob/5900b4c6829b0bdbe69e1f95fb74e935bc8f33d4/datafusion/src/physical_plan/datetime_expressions.rs#L85
   
   In my view, if the existing behavior has to stay, it has to stay; but if it _doesn't_ have to stay, then as a user I'd feel much more comfortable if DataFusion didn't expose the server's timezone -- or at least made it a session variable, as Postgres does.
   
   Note that we're up to _four_ timezones in every call to `TO_TIMESTAMP()`:
   
   * `NULL`: "localtime" the Arrow concept -- calendar-date-plus-wall-time
   * `UTC`
   * The input-string timezone offset
   * DataFusion server's "local timezone" (distinct from Arrow's "local time", and presumably unknown to my users)
   
   My use case is to run custom SQL from the general public. I realize I can use environment variables to force DataFusion's "server timezone" to be UTC; but that doesn't negate the learning curve.
   
   Postgres has the same complexity. Its syntax:
   
   * `SELECT timestamp_with_time_zone AT TIME ZONE 'America/Eastern'` => converts UTC to localtime
   * `SELECT timestamp_with_time_zone::TIMESTAMP` => converts UTC to session-timezone localtime
   * `SELECT timestamp_without_time_zone AT TIME ZONE 'America/Eastern' => converts localtime to UTC
   * `SELECT timestamp_without_time_zone::TIMESTAMPTZ` => converts localtime to UTC using session-timezone localtime
   
   ... and I'll bet most Postgres misunderstand these concepts for years until they figure it out.


-- 
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@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org