You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Oliver Lee (Jira)" <ji...@apache.org> on 2023/01/13 21:41:00 UTC
[jira] [Updated] (CALCITE-5476) Add DATETIME_TRUNC for BigQuery
[ https://issues.apache.org/jira/browse/CALCITE-5476?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Oliver Lee updated CALCITE-5476:
--------------------------------
Description:
Adding in DATETIME_TRUNC() to the BigQuery library as described here: [https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_trunc]
Truncates a {{DATETIME}} value to the granularity of {{{}date_time_part{}}}. The {{DATETIME}} value is always rounded to the beginning of {{{}date_time_part{}}}, which can be one of the following:
* {{{}MICROSECOND{}}}: If used, nothing is truncated from the value.
* {{{}MILLISECOND{}}}: The nearest lessor or equal millisecond.
* {{{}SECOND{}}}: The nearest lessor or equal second.
* {{{}MINUTE{}}}: The nearest lessor or equal minute.
* {{{}HOUR{}}}: The nearest lessor or equal hour.
* {{{}DAY{}}}: The day in the Gregorian calendar year that contains the {{DATETIME}} value.
* {{{}WEEK{}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on Sundays. {{WEEK}} is equivalent to {{{}WEEK(SUNDAY){}}}.
* {{{}WEEK(WEEKDAY){}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on {{{}WEEKDAY{}}}. {{WEEKDAY}} must be one of the following: {{{}SUNDAY{}}}, {{{}MONDAY{}}}, {{{}TUESDAY{}}}, {{{}WEDNESDAY{}}}, {{{}THURSDAY{}}}, {{{}FRIDAY{}}}, or {{{}SATURDAY{}}}.
* {{{}ISOWEEK{}}}: The first day of the [ISO 8601 week|https://en.wikipedia.org/wiki/ISO_week_date] in the ISO week that contains the {{DATETIME}} value. The ISO week begins on Monday. The first ISO week of each ISO year contains the first Thursday of the corresponding Gregorian calendar year.
* {{{}MONTH{}}}: The first day of the month in the month that contains the {{DATETIME}} value.
* {{{}QUARTER{}}}: The first day of the quarter in the quarter that contains the {{DATETIME}} value.
* {{{}YEAR{}}}: The first day of the year in the year that contains the {{DATETIME}} value.
* {{{}ISOYEAR{}}}: The first day of the [ISO 8601|https://en.wikipedia.org/wiki/ISO_8601] week-numbering year in the ISO year that contains the {{DATETIME}} value. The ISO year is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
{{SELECT DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) -> 2008-12-25T00:00:00}}
was:
Adding in DATETIME_TRUNC() to the BigQuery library as described here: [https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_trunc]
Truncates a {{DATETIME}} value to the granularity of {{{}date_time_part{}}}. The {{DATETIME}} value is always rounded to the beginning of {{{}date_time_part{}}}, which can be one of the following:
* {{{}MICROSECOND{}}}: If used, nothing is truncated from the value.
* {{{}MILLISECOND{}}}: The nearest lessor or equal millisecond.
* {{{}SECOND{}}}: The nearest lessor or equal second.
* {{{}MINUTE{}}}: The nearest lessor or equal minute.
* {{{}HOUR{}}}: The nearest lessor or equal hour.
* {{{}DAY{}}}: The day in the Gregorian calendar year that contains the {{DATETIME}} value.
* {{{}WEEK{}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on Sundays. {{WEEK}} is equivalent to {{{}WEEK(SUNDAY){}}}.
* {{{}WEEK(WEEKDAY){}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on {{{}WEEKDAY{}}}. {{WEEKDAY}} must be one of the following: {{{}SUNDAY{}}}, {{{}MONDAY{}}}, {{{}TUESDAY{}}}, {{{}WEDNESDAY{}}}, {{{}THURSDAY{}}}, {{{}FRIDAY{}}}, or {{{}SATURDAY{}}}.
* {{{}ISOWEEK{}}}: The first day of the [ISO 8601 week|https://en.wikipedia.org/wiki/ISO_week_date] in the ISO week that contains the {{DATETIME}} value. The ISO week begins on Monday. The first ISO week of each ISO year contains the first Thursday of the corresponding Gregorian calendar year.
* {{{}MONTH{}}}: The first day of the month in the month that contains the {{DATETIME}} value.
* {{{}QUARTER{}}}: The first day of the quarter in the quarter that contains the {{DATETIME}} value.
* {{{}YEAR{}}}: The first day of the year in the year that contains the {{DATETIME}} value.
* {{{}ISOYEAR{}}}: The first day of the [ISO 8601|https://en.wikipedia.org/wiki/ISO_8601] week-numbering year in the ISO year that contains the {{DATETIME}} value. The ISO year is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
{{SELECT DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) -> 2008-12-25T00:00:00}}
{{}}
{{}}
> Add DATETIME_TRUNC for BigQuery
> -------------------------------
>
> Key: CALCITE-5476
> URL: https://issues.apache.org/jira/browse/CALCITE-5476
> Project: Calcite
> Issue Type: Improvement
> Reporter: Oliver Lee
> Assignee: Oliver Lee
> Priority: Minor
>
> Adding in DATETIME_TRUNC() to the BigQuery library as described here: [https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_trunc]
> Truncates a {{DATETIME}} value to the granularity of {{{}date_time_part{}}}. The {{DATETIME}} value is always rounded to the beginning of {{{}date_time_part{}}}, which can be one of the following:
> * {{{}MICROSECOND{}}}: If used, nothing is truncated from the value.
> * {{{}MILLISECOND{}}}: The nearest lessor or equal millisecond.
> * {{{}SECOND{}}}: The nearest lessor or equal second.
> * {{{}MINUTE{}}}: The nearest lessor or equal minute.
> * {{{}HOUR{}}}: The nearest lessor or equal hour.
> * {{{}DAY{}}}: The day in the Gregorian calendar year that contains the {{DATETIME}} value.
> * {{{}WEEK{}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on Sundays. {{WEEK}} is equivalent to {{{}WEEK(SUNDAY){}}}.
> * {{{}WEEK(WEEKDAY){}}}: The first day of the week in the week that contains the {{DATETIME}} value. Weeks begin on {{{}WEEKDAY{}}}. {{WEEKDAY}} must be one of the following: {{{}SUNDAY{}}}, {{{}MONDAY{}}}, {{{}TUESDAY{}}}, {{{}WEDNESDAY{}}}, {{{}THURSDAY{}}}, {{{}FRIDAY{}}}, or {{{}SATURDAY{}}}.
> * {{{}ISOWEEK{}}}: The first day of the [ISO 8601 week|https://en.wikipedia.org/wiki/ISO_week_date] in the ISO week that contains the {{DATETIME}} value. The ISO week begins on Monday. The first ISO week of each ISO year contains the first Thursday of the corresponding Gregorian calendar year.
> * {{{}MONTH{}}}: The first day of the month in the month that contains the {{DATETIME}} value.
> * {{{}QUARTER{}}}: The first day of the quarter in the quarter that contains the {{DATETIME}} value.
> * {{{}YEAR{}}}: The first day of the year in the year that contains the {{DATETIME}} value.
> * {{{}ISOYEAR{}}}: The first day of the [ISO 8601|https://en.wikipedia.org/wiki/ISO_8601] week-numbering year in the ISO year that contains the {{DATETIME}} value. The ISO year is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
>
>
> {{SELECT DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) -> 2008-12-25T00:00:00}}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)