[CALCITE-4134] Interval expressions

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.23.0
• Fix Version/s: 1.25.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 20/Jul/20 20:21
• Updated: 13/Nov/23 10:03
• Resolved: 29/Jul/20 01:08

Description

Support interval expressions.

Standard SQL has only interval literals (e.g. INTERVAL '1' HOUR, INTERVAL -'1:2' HOUR TO MINUTE). Note that the 'value' part is a character literal and may contain formatting characters like ':'.

Interval expressions are a SQL extension present in MySQL and BigQuery. We support the syntax INTERVAL numericExpression timeUnit:

• the 'value' part is a numeric expression;
• timeUnit is a time unit (such as HOUR), not a time unit range (such as HOUR TO MINUTE);
• timeUnit may have precision, e.g. HOUR(2);
• timeUnit may be plural, e.g. HOURS, if SqlConformance.allowPluralTimeUnits (see CALCITE-3383);
• numeric literals and identifiers do not require parentheses, but other expressions require parentheses.

Examples:

• SELECT INTERVAL empno HOUR FROM Emp
• SELECT INTERVAL (empno * 2) MINUTE FROM Emp
• INTERVAL -3 YEAR

Issue Links

• is related to: CALCITE-6114 RexExecutor fails on interval expressions with fractional second parts
• relates to: CALCITE-5659 Allow CAST in an INTERVAL definition
• relates to: CALCITE-4089 In Babel, allow 'CAST(integer AS DATE)' even though it is illegal in Calcite SQL
• relates to: CALCITE-5776 Support for alternative Interval syntax