[jira] [Commented] (CALCITE-6269) Fix missing/broken BigQuery date-time format elements
[
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]
{{}} 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"
> *
> [S|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)
[jira] [Commented] (CALCITE-6269) Fix missing/broken BigQuery date-time format elements
[
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
> *
> [|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"
> *
> [S|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 '.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)
