[CALCITE-5508] Add constructor functions for DATE, TIME, TIMESTAMP, DATETIME

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.34.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 30/Jan/23 21:14
• Updated: 23/Nov/23 00:24
• Resolved: 10/Feb/23 17:00

Description

Add "constructor" functions for DATE, TIME, TIMESTAMP, DATETIME. These are functions such as DATE(1969, 7, 20) that create date-time types and we informally call them "constructors".

The functions are based on those in BigQuery, and would be enabled in the BigQuery library. Their names follow the terminology of BigQuery's type system, which is confusing when working in Calcite's type system. For example:

• The function call DATETIME(1969, 7, 20, 16, 17, 40) returns a Calcite TIMESTAMP, a type that is called DATETIME in BigQuery. (Eagle landed at 4.17pm Houston time.)
• Similarly, TIMESTAMP(DATE '1969-07-21', TIME '22:17:40') returns a Calcite TIMESTAMP WITH LOCAL TIME ZONE representing the same instant in UTC. That type is called TIMESTAMP in BigQuery.

Issue Links

• is duplicated by: CALCITE-5164 Planner#parser can't parse TIMESTAMP() function
• is related to: CALCITE-5446 Support TIMESTAMP WITH LOCAL TIME ZONE type in JDBC driver
• is related to: CALCITE-5424 Customize handling of literals based on type system
• is related to: CALCITE-5498 BigQuery TIMESTAMP() function short notation for timezone offsets isn’t supported in Java 8
• relates to: CALCITE-5496 Support time zones when parsing TIMESTAMP WITH LOCAL TIME ZONE
• links to: GitHub Pull Request #3521