Oliver Lee created CALCITE-5476:
-----------------------------------
Summary: 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
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)
