[ https://issues.apache.org/jira/browse/CALCITE-6269?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jerin John updated CALCITE-6269: -------------------------------- Description: 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 * [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] - 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 * [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. was: 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 * [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. > 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 > > 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 > * > [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] > - 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 > * [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)