[
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
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]
- 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.
> 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
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
