[
https://issues.apache.org/jira/browse/CALCITE-7494?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18082918#comment-18082918
]
Mihai Budiu commented on CALCITE-7494:
--------------------------------------
According to one of these LLMs, only Oracle stores the original IANA time zone.
SqlServer and Snowflake normalize the zone to an offset, which cannot recover
the original time zone.
Other major databases only store the TIMESTAMP in UTC: Postgres, MySQL,
BigQuery, DuckDB, Redshift.
Of course, the LLM could be wrong.
> Avatica conversion to string of TIMESTAMP WITH TIME ZONE does not include
> time zone
> -----------------------------------------------------------------------------------
>
> Key: CALCITE-7494
> URL: https://issues.apache.org/jira/browse/CALCITE-7494
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Affects Versions: avatica-1.27.0
> Reporter: Mihai Budiu
> Priority: Minor
>
> Here is the output of a quidem test from CALCITE-7491 (after fixing the
> issue):
> {code:java}
> select TIMESTAMP WITH TIME ZONE '2020-01-01 00:00:00 America/New_York';
> +---------------------+
> | EXPR$0 |
> +---------------------+
> | 2020-01-01 05:00:00 |
> +---------------------+{code}
> Note that the timestamp does not include time zone information.
> There are two mainstream representations of runtime values of TIMESTAMP WITH
> TIME ZONE: most databases just normalize them to UTC timezones and discard
> the timezone information. A couple of databases preserve the time zone as
> well. This is an important distinction, because it affects when two such
> timestamps are equal.
> I am proposing to implement the simpler definition.
> A second question is about the string representation of such a timestamp.
> Postgres converts it into the session timezone. An alternative would be to
> always display it in the UTC timezone. The latter approach has the nice
> property that it's deterministic for writing tests.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
