You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Zhenghua Gao <do...@gmail.com> on 2019/12/19 07:47:01 UTC
[DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME
ZONE types to Calcite
Hi,
As Consistent timestamp types in Hadoop SQL engines
<https://docs.google.com/document/d/1gNRww9mZJcHvUDCXklzjFEQGpefsuR_akCDfWsdE35Q/edit#heading=h.5bmvfrjnmkqx>
[1] described,
there are 3 semantics of TIMESTAMP
types all round. Different database vendors and SQL engines implemented
different behaviors and
even different revisions of the SQL standard define timestamp types
differently(See the following depicts)
In CALCITE-1947 [2], TIME/TIMESTAMP WITH LOCAL TIME ZONE (Instant
semantics) was introduced and
the implementation was similar to PostgreSQL's TIME/TIMESTAMP WITH TIME
ZONE (yes, they are making
the mistake to implement the *Instant* semantics for TIME/TIMESTAMP WITH
TIME ZONE types).
CALCITE-208 [3] and CALCITE-1784 [4] want to introduce TIMESTAMP WITH TIME
ZONE and there is no progress for a long time.
So IMO we should restart the work of introducing TIME/TIMESTAMP WITH TIME
ZONE types to Calcite for
the following reasons:
- It's a SQL Standard data type
- Some database vendors(ORACLE/SAP/DB2/MS SQL) and SQL
engines(presto/snowflake) already support it
- integrate easily for downstream projects
By introducing there types to Calcite, the following works should be
discussed and done:
- Add TIME/TIMESTAMP WITH TIME ZONE types to optimizer
- Confirm the physical storage of there types in Calcite (an extra
information of time zone should be stored)
- Extend Calcite SQL parser to support new types
- Confirm the behaviors of builtin operators: CASTing, extract
- Others (please remind me if I miss anything)
What do you think? cc @Danny
[1]
https://docs.google.com/document/d/1gNRww9mZJcHvUDCXklzjFEQGpefsuR_akCDfWsdE35Q/edit#
[2] https://issues.apache.org/jira/browse/CALCITE-1947
[3] https://issues.apache.org/jira/browse/CALCITE-208
[4] https://issues.apache.org/jira/browse/CALCITE-1784
*Best Regards,*
*Zhenghua Gao*
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Zhenghua Gao <do...@gmail.com>.
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 <am...@apache.org> 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 <do...@gmail.com> 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 <am...@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 <do...@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 <do...@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.vladimir@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
> > > > > >>
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Rui Wang <am...@apache.org>.
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 <do...@gmail.com> 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 <am...@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 <do...@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 <do...@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.vladimir@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
> > > > >>
> > > >
> > > >
> > >
> >
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Zhenghua Gao <do...@gmail.com>.
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 <am...@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 <do...@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 <do...@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.vladimir@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
> > > >>
> > >
> > >
> >
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Rui Wang <am...@apache.org>.
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 <do...@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 <do...@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.vladimir@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
> > >>
> >
> >
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Zhenghua Gao <do...@gmail.com>.
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 <do...@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.vladimir@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
> >>
>
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Julian Hyde <jh...@apache.org>.
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 <do...@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.vladimir@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
>>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Zhenghua Gao <do...@gmail.com>.
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.vladimir@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
>
Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH
TIME ZONE types to Calcite
Posted by Vladimir Sitnikov <si...@gmail.com>.
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