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 > > > > >> > > > > > > > > > > > > > >
