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*
