[
https://issues.apache.org/jira/browse/CALCITE-6269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17819021#comment-17819021
]
Jerin John commented on CALCITE-6269:
-------------------------------------
Hi [~mbudiu], these issues are definitely related and I can verify that current
implementation of {{FormatModels}} hasn't defined the format specifiers you've
highlighted.
I was tracking all the other format elements not available/broken when testing
for [CALCITE-2980|https://issues.apache.org/jira/browse/CALCITE-2980], wanted
to implement them as a follow-up to initial support for FORMAT clause in CAST.
> Fix missing/broken BigQuery date-time format elements
> -----------------------------------------------------
>
> Key: CALCITE-6269
> URL: https://issues.apache.org/jira/browse/CALCITE-6269
> Project: Calcite
> Issue Type: Bug
> Reporter: Jerin John
> Assignee: Jerin John
> Priority: Minor
>
> Add support for missing/broken DATE-TIME format elements:
> * [YYY /
> Y|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string]
> - last three or 1 digits of year
> *
> [YYYY|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string]
> - supports four or more digits in the year, Calcite using
> [DateString|https://github.com/apache/calcite/blob/3326475c766267d521330006cc80730c4e456191/core/src/main/java/org/apache/calcite/util/DateString.java]
> util throws:
> {{java.lang.IllegalArgumentException: Year out of range: [12018]}}
> *
> [MONTH|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_month_as_string]
> formats to "Jan" instead of "JANUARY"
> *
> [SSSSS|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string]
> - five digit seconds precision, only SS two digit precision is available
> * [FFn
> (n=1/2)|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_second_as_string]
> - 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|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_meridian_as_string]
> - Meridian formats not available
> * [Parsing timestamp
> literals|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string]
> with timezones as used by BQ does not seem to be supported yet (format
> element TZR is unimplemented, BQ has TZH, TZM for hour and minute offsets)
> (eg: {{cast('2020.06.03 00:00:53+00' as timestamp format 'YYYY.MM.DD
> HH:MI:SSTZH')}}
> * BQ format [timezone as string
> |https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string]
> can take an additional argument {{{}AT TIME ZONE 'Asia/Kolkata'{}}}, which
> would require additional parser changes and time zone parameter to be plumbed
> in to the cast operator call.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
