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