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 <[email protected]> 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
