[CALCITE-6269] Fix missing/broken BigQuery date-time format elements

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Labels: pull-request-available
• Assignee: Jerin John
• Reporter: Jerin John
• Created: 17/Feb/24 01:31
• Updated: 22/Apr/24 20:24
• Resolved: 22/Apr/24 16:37

Description

Calcite has the FormatModels class which is missing support for or has incorrect implementation for the following DATE-TIME format elements:

• YYY / Y - last three or 1 digits of year
• MONTH formats to "Jan" instead of "JANUARY"

• SSSSS - seconds of the day (5 digits), only SS is available that gives seconds of the minute.
• FFn (n=1/2) - always returns seconds with precision 3 (=FF3); also BQ supports n=1-9, calcite format models supports n=1-6, should we expand this range?
• AM/PM - Meridian formats not available

Issue Links

• is related to: CALCITE-6253 BigQuery FORMAT_DATE function does not implement all format specifiers
• is related to: CALCITE-2980 Implement the FORMAT clause of the CAST operator
• relates to: CALCITE-6358 Support all PostgreSQL 14 date/time patterns for to_char
• relates to: CALCITE-6367 Add timezone support for FORMAT clause in CAST (enabled in BigQuery)
• relates to: CALCITE-6375 Enable failing quidem tests for FORMAT in CAST
• links to: GitHub Pull Request #3761