Hi Rui, Thanks for your reply.
I also prefer [long + TimeZone] as internal representation of TIMESTAMP WITH TIME ZONE, and [int + TimeZone] as representation of TIME WITH TIME ZONE for the following reasons: 1) Current Calcite codebase use java.sql.TimeZone to describe time zone information(as you mentioned above) 2) [long/int + TimeZone] is simple and clear for the semantic of TIME/TIMESTAMP WITH TIME ZONE *Best Regards,* *Zhenghua Gao* On Sat, Dec 21, 2019 at 3:30 AM Rui Wang <[email protected]> wrote: > In general, I incline to a solution of saving timestamp with time zone by > [long + a format of time zone]. The long is the epoch millis in UTC and the > time zone is the original zone of the value. Using UTC epoch millis will > simplify comparison and make instant semantic based functions reusable. > Zone information will be useful for zone dependent functions. E.g. > Extract() that extracts which "DAY" or format() that print a string which > should have the right time zone included. > > The above is more conceptual, as many existing Java classes has saving it > in that way, and implementing a class in Calcite can achieve the same goal. > I think people who have more experience in Calcite runtime code might could > better answer it and say which solution is better for > function implementation, enumerable implementation. etc. > > > Regarding Zhenghua's investigation of different types of time zones, in > codebase I found [1], does it mean that in the past Calcite community > decides to adopt the time zone format defined in [2]? > > > [1]: > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/util/TimestampWithTimeZoneString.java#L38 > [2]: https://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html > > -Rui > > > > On Fri, Dec 20, 2019 at 2:42 AM Zhenghua Gao <[email protected]> wrote: > > > Hi > > > > I did some research for the storage type for TIME/TIMESTAMP WITH TIME > ZONE > > types, and > > want to share some ideas for discussion: > > > > - There are three types of time zone information for both > > java.sql.TimeZone and java.time.ZoneId > > - Region-based time zone, such as: > > Asia/Shanghai, America/Los_Angeles, etc > > - Offset-based time zone, such > > as: Etc/GMT, Etc/GMT+1, GMT+08:30/+08:30, etc > > - Some abbreviations which have been deprecated in > java.sql.TimeZone, > > such as: EST, HST, ACT > > > > The abbreviations are always mapped to the first two types, > we > > can ignore them in the following discussion. > > > > - java.time.OffsetTime/java.time.OffsetDateTime can't be storage types > > because they only covers the second type of time zone information > > - So we should introduce our own internal classes to represent there > > types and there are 3 choice: > > - Use (int + TimeZone) to represent TIME WITH TIME ZONE, (long > > +Timezone) to represent TIMESTAMP WITH TIME ZONE > > - Use (int + ZoneId) to represent TIME WITH TIME ZONE, (long + > > ZoneId) to represent TIMESTAMP WITH TIME ZONE > > - Presto style > > > > Presto provide a *zone-index.properties* which > > contains the fixed number key (a short value) for every supported time > > zone id (a string), > > and use a single long value to store the > millisecond > > and time zone key > > > > What do you think which one should be our storage solution? > > > > *Best Regards,* > > *Zhenghua Gao* > > > > > > On Fri, Dec 20, 2019 at 2:51 PM Rui Wang <[email protected]> wrote: > > > > > Thanks Zhenghua sharing [1], which really explaining three different > > > semantics of TIMESTAMP and clarified some of my long term confusion > about > > > TIMESTAMP. > > > > > > > > > Julian> We need all 3, regardless of what they are called > > > Can I confirm that Calcite already have the following two semantics > > > support: > > > > > > 1. timestamp that has (number) content and “zoneless” semantics (I > > believe > > > it is TIMESTAMP, alternatively it might be named as > > > TIMESTAMP_WITIOUT_TIME_ZONE) > > > 2. a timestamp type with (number) content and “instant” semantics > (which > > I > > > believe it is the TIME_WITH_LOCAL_TIME_ZONE > > > > > > > > > What I am interested in is how's the current support of the first > > semantic. > > > And if it does not have well support, I would like to work on it to > make > > it > > > better. (In the past I don't really find the first semantic exists in > > > Calcite, maybe I have missed something). > > > > > > [1]: > > > > > > > > > https://docs.google.com/document/d/1gNRww9mZJcHvUDCXklzjFEQGpefsuR_akCDfWsdE35Q/edit# > > > > > > -Rui > > > > > > > > > On Thu, Dec 19, 2019 at 6:27 PM Zhenghua Gao <[email protected]> wrote: > > > > > > > Thanks for your comments! > > > > I have opened an umbrella issue[1] to track this. > > > > > > > > [1] https://issues.apache.org/jira/browse/CALCITE-3611 > > > > > > > > *Best Regards,* > > > > *Zhenghua Gao* > > > > > > > > > > > > On Fri, Dec 20, 2019 at 4:05 AM Julian Hyde <[email protected]> > wrote: > > > > > > > > > It would be great to have a timestamp type with (timeZone, number) > > data > > > > > content, and also a timestamp type with (number) content and > > “instant” > > > > > semantics, in addition to the current timestamp that has (number) > > > content > > > > > and “zoneless” semantics. (I’m avoiding labeling these with SQL > type > > > > names, > > > > > since this is a bit contentious. We need all 3, regardless of what > > they > > > > are > > > > > called.) > > > > > > > > > > We also need improvements to the JDBC driver. Part of the reason > that > > > > > timestamps and time zones are so confusing in SQL is because of the > > > > mapping > > > > > to Java types. The new(ish) package java.time has classes that are > > huge > > > > > improvements over java.sql.Timestamp. As part of this work, I > would > > > like > > > > > the JDBC driver to support reading and writing java.time.Instant, > > > > > java.time.LocalDateTime and java.time.ZonedDateTime. > > > > > > > > > > Julian > > > > > > > > > > [1] > > > > > > > > > https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html > > > > < > > > > > > > > > https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html > > > > > > > > > > > > > > > > On Dec 19, 2019, at 12:43 AM, Zhenghua Gao <[email protected]> > > wrote: > > > > > > > > > > > > You are right. PostgreSQL's TIMESTAMP WITH TIME ZONE has > "Instant" > > > > > > semantics. > > > > > > That's the reason that CALCITE-1947 change the type as "TIMESTAMP > > > WITH > > > > > > LOCAL TIME ZONE" > > > > > > > > > > > > *Best Regards,* > > > > > > *Zhenghua Gao* > > > > > > > > > > > > > > > > > > On Thu, Dec 19, 2019 at 4:17 PM Vladimir Sitnikov < > > > > > > [email protected]> wrote: > > > > > > > > > > > >> Zhenghua>the implementation was similar to PostgreSQL's > > > > > >> > > > > > >> PostgreSQL DB stores timestamps similar to "UNIX timestamp" (it > > uses > > > > > int8), > > > > > >> and it does that for both "with" and "without" time zone. > > > > > >> In other words, PostgreSQL cannot have "OffsetDateTime" > semantics > > :( > > > > > >> > > > > > >> Vladimir > > > > > >> > > > > > > > > > > > > > > > > > > > >
