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 <amaliu...@apache.org> 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 <doc...@gmail.com> 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 <jh...@apache.org> 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 <doc...@gmail.com> 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 <
> > > > sitnikov.vladi...@gmail.com> 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
> > > >>
> > >
> > >
> >
>
