[
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)
