[jira] [Commented] (CALCITE-6269) Fix missing/broken BigQuery date-time format elements

["Jerin John (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17837361#comment-17837361 ] 

Jerin John commented on CALCITE-6269:
-------------------------------------

Hi committers, I've raised this [PR|https://github.com/apache/calcite/pull/3761] that should make available fixes for most of the above missing use cases.

Couple of points to note, as observed and highlighted in this comment on CALCITE-6315, the {{SimpleDateFormat}} class which currently stores the datetime object only supports parsing up to 3 decimal places for values like Fractional Seconds. This hinders our ability to produce any value for FFn elements (e.g. FF4, FF5, .. FF9) as precision above 3 is not available in this parsed object. My temporary fix was to pad zeroes to the right, as the existing implementation padded to the left and returned an incorrect answer as seen in that comment. As per BQ [docs,|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_year_as_string] {{YYYY}} format is said to support 4 or more digits as an year value which was incorrect as BQ studio also threw a similar error as calcite would, i.e. incorrect literal supplied as year.

Additionally, I have moved the last two features mentioned previously in the description, regarding support for Timezone format elements, to a new ticket CALCITE-6367 as it requires more rework than fixing existing elements or adding similar ones. Would appreciate a review on the PR and comments on how to handle these unfixed cases and timezone support for later.

> 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
>              Labels: pull-request-available
>
> Calcite has the [FormatModels|https://github.com/apache/calcite/blob/2dadcd1a0e235f5fe1b29c9c32014035971fd45e/core/src/main/java/org/apache/calcite/util/format/FormatModels.java#L115] class which is missing support for or has incorrect implementation for the following 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
>  * [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] - seconds of the day (5 digits), only SS is available that gives seconds of the minute.
>  * [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



--
This message was sent by Atlassian Jira
(v8.20.10#820010)