Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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.

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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,

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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.

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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 wrote: > It would be great to have a timestamp type with (timeZone, number) data >

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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,

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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

Re: [DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

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

[DISCUSS] Restart the work of introducing TIME/TIMESTAMP WITH TIME ZONE types to Calcite

Hi, As Consistent timestamp types in Hadoop SQL engines [1] described, there are 3 semantics of TIMESTAMP types all round. Different database vendors and SQL engines implemented different