You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Vladimir Sitnikov <si...@gmail.com> on 2021/04/01 08:58:40 UTC
Re: Time zone management and DST in Avatica
Vladimir>I can write a dedicated test for that, but it would require some
time. But
Vladimir>I do not see much value in it during the discussion
What is your question then?
I assume you want to make a change to Avatica.
Typically, we need tests for all code changes, so eventually, a test would
be required anyway.
Timezones are always hard to get, so there's no question there's
something wrong with timezones in Avatica.
You don't need to question that, the defect is there, so let's focus on the
most important end-to-end cases.
---
It looks like Avatica uses "int" to represent all time-like entities, which
is not really enough to distinguish "timestamp without time zone" vs
"timestamp with time zone".
On top of that, databases might support per-cell timezone values.
For instance, PostgreSQL always stores all "timestamp with time zone"
values in UTC, so users can't store "Europe/Amsterdam" in the DB. The
values would always be normalized to UTC.
On contrary, OracleDB can store time zone as a part of the value, so if the
user stores "09:00 Europe/Amsterdam", then Europe/Amsterdam becomes a part
of the value which is stored in the DB.
---
I believe "offset calculation" can not be discussed without end-to-end
sample.
Just in case, "sqlfiddle, psql, etc" do not count since SQL clients do
impact the results since
SQL client sets connection environment (e.g. client_timestamp), SQL client
can use its own timestamp formatting, etc.
That is why I suggest focusing on JDBC API rather than sqlfiddle.
We can discuss whether Avatica needs "local timestamp" datatype like
"2021-04-01 03:05 local time" (~LocalDateTime)
We can discuss whether Avatica needs "timestamp with time zone" datatype
"2021-04-01 03:05 Europe/Amsterdam" (~ZonedDateTime).
We can discuss whether Avatica needs a per-value timezone: should Avatica
normalize user-provided instants to UTC or should it propagate per-value
timezones to the underlying engine.
Does that make sense?
Vladimir
Re: Time zone management and DST in Avatica
Posted by Julian Hyde <jh...@apache.org>.
> I guess anyone who wants the latter would use "Etc/GMT+2".
Oops. That would be "Etc/GMT-2".
On Thu, Apr 1, 2021 at 11:17 AM Julian Hyde <jh...@apache.org> wrote:
>
> Let me state up front: I do think there is an issue here, and we can
> discuss it in another email. But I want to make a couple of pedantic
> points.
>
> Time zones are hard to implement and hard to understand. Many times
> over the years, people have accused Avatica of having the wrong
> behavior, and usually they have been mistaken. Therefore if you are
> claiming that Avatica is wrong you need to provide a watertight case.
>
> Vladimir Ozerov's original example was in terms of the "Israel
> Standard Time" time zone, and he later switched to the "Israel" time
> zone and claimed there was no difference. Vladimir claims that "Israel
> Standard Time" is variable; but according to Wikipedia [1] it is
> fixed. On my system, Java's TimeZones includes "Israel" and
> "Asia/Jerusalem", which are variable, but does not include "Israel
> Standard Time". I guess anyone who wants the latter would use
> "Etc/GMT+2".
>
> Julian
>
> [1] https://en.wikipedia.org/wiki/Israel_Standard_Time
>
> On Thu, Apr 1, 2021 at 1:58 AM Vladimir Sitnikov
> <si...@gmail.com> wrote:
> >
> > Vladimir>I can write a dedicated test for that, but it would require some
> > time. But
> > Vladimir>I do not see much value in it during the discussion
> >
> > What is your question then?
> > I assume you want to make a change to Avatica.
> > Typically, we need tests for all code changes, so eventually, a test would
> > be required anyway.
> >
> > Timezones are always hard to get, so there's no question there's
> > something wrong with timezones in Avatica.
> > You don't need to question that, the defect is there, so let's focus on the
> > most important end-to-end cases.
> >
> > ---
> >
> > It looks like Avatica uses "int" to represent all time-like entities, which
> > is not really enough to distinguish "timestamp without time zone" vs
> > "timestamp with time zone".
> > On top of that, databases might support per-cell timezone values.
> >
> > For instance, PostgreSQL always stores all "timestamp with time zone"
> > values in UTC, so users can't store "Europe/Amsterdam" in the DB. The
> > values would always be normalized to UTC.
> > On contrary, OracleDB can store time zone as a part of the value, so if the
> > user stores "09:00 Europe/Amsterdam", then Europe/Amsterdam becomes a part
> > of the value which is stored in the DB.
> >
> > ---
> >
> > I believe "offset calculation" can not be discussed without end-to-end
> > sample.
> > Just in case, "sqlfiddle, psql, etc" do not count since SQL clients do
> > impact the results since
> > SQL client sets connection environment (e.g. client_timestamp), SQL client
> > can use its own timestamp formatting, etc.
> > That is why I suggest focusing on JDBC API rather than sqlfiddle.
> >
> > We can discuss whether Avatica needs "local timestamp" datatype like
> > "2021-04-01 03:05 local time" (~LocalDateTime)
> > We can discuss whether Avatica needs "timestamp with time zone" datatype
> > "2021-04-01 03:05 Europe/Amsterdam" (~ZonedDateTime).
> > We can discuss whether Avatica needs a per-value timezone: should Avatica
> > normalize user-provided instants to UTC or should it propagate per-value
> > timezones to the underlying engine.
> >
> > Does that make sense?
> >
> > Vladimir
Re: Time zone management and DST in Avatica
Posted by Julian Hyde <jh...@apache.org>.
Let me state up front: I do think there is an issue here, and we can
discuss it in another email. But I want to make a couple of pedantic
points.
Time zones are hard to implement and hard to understand. Many times
over the years, people have accused Avatica of having the wrong
behavior, and usually they have been mistaken. Therefore if you are
claiming that Avatica is wrong you need to provide a watertight case.
Vladimir Ozerov's original example was in terms of the "Israel
Standard Time" time zone, and he later switched to the "Israel" time
zone and claimed there was no difference. Vladimir claims that "Israel
Standard Time" is variable; but according to Wikipedia [1] it is
fixed. On my system, Java's TimeZones includes "Israel" and
"Asia/Jerusalem", which are variable, but does not include "Israel
Standard Time". I guess anyone who wants the latter would use
"Etc/GMT+2".
Julian
[1] https://en.wikipedia.org/wiki/Israel_Standard_Time
On Thu, Apr 1, 2021 at 1:58 AM Vladimir Sitnikov
<si...@gmail.com> wrote:
>
> Vladimir>I can write a dedicated test for that, but it would require some
> time. But
> Vladimir>I do not see much value in it during the discussion
>
> What is your question then?
> I assume you want to make a change to Avatica.
> Typically, we need tests for all code changes, so eventually, a test would
> be required anyway.
>
> Timezones are always hard to get, so there's no question there's
> something wrong with timezones in Avatica.
> You don't need to question that, the defect is there, so let's focus on the
> most important end-to-end cases.
>
> ---
>
> It looks like Avatica uses "int" to represent all time-like entities, which
> is not really enough to distinguish "timestamp without time zone" vs
> "timestamp with time zone".
> On top of that, databases might support per-cell timezone values.
>
> For instance, PostgreSQL always stores all "timestamp with time zone"
> values in UTC, so users can't store "Europe/Amsterdam" in the DB. The
> values would always be normalized to UTC.
> On contrary, OracleDB can store time zone as a part of the value, so if the
> user stores "09:00 Europe/Amsterdam", then Europe/Amsterdam becomes a part
> of the value which is stored in the DB.
>
> ---
>
> I believe "offset calculation" can not be discussed without end-to-end
> sample.
> Just in case, "sqlfiddle, psql, etc" do not count since SQL clients do
> impact the results since
> SQL client sets connection environment (e.g. client_timestamp), SQL client
> can use its own timestamp formatting, etc.
> That is why I suggest focusing on JDBC API rather than sqlfiddle.
>
> We can discuss whether Avatica needs "local timestamp" datatype like
> "2021-04-01 03:05 local time" (~LocalDateTime)
> We can discuss whether Avatica needs "timestamp with time zone" datatype
> "2021-04-01 03:05 Europe/Amsterdam" (~ZonedDateTime).
> We can discuss whether Avatica needs a per-value timezone: should Avatica
> normalize user-provided instants to UTC or should it propagate per-value
> timezones to the underlying engine.
>
> Does that make sense?
>
> Vladimir