Skip to content

Latest commit

 

History

History
828 lines (618 loc) · 21 KB

date_functions.md

File metadata and controls

828 lines (618 loc) · 21 KB
Raw

Date functions

ZetaSQL supports the following date functions.

CURRENT_DATE

CURRENT_DATE()
CURRENT_DATE(time_zone_expression)
CURRENT_DATE

Description

Returns the current date.

This function supports the following arguments:

  • time_zone_expression: A STRING expression that represents a time zone. If no time zone is specified, the default time zone, which is implementation defined, is used. If this expression is used and it evaluates to NULL, this function returns NULL.

Return Data Type

DATE

Examples

The following query produces the current date in the default time zone:

SELECT CURRENT_DATE() AS the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

The following queries produce the current date in a specified time zone:

SELECT CURRENT_DATE('America/Los_Angeles') AS the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+
SELECT CURRENT_DATE('-08') AS the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

The following query produces the current date in the default time zone. Parentheses are not needed if the function has no arguments.

SELECT CURRENT_DATE AS the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

When a column named current_date is present, the column name and the function call without parentheses are ambiguous. To ensure the function call, add parentheses; to ensure the column name, qualify it with its range variable. For example, the following query will select the function in the the_date column and the table column in the current_date column.

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

+------------+--------------+
| the_date   | current_date |
+------------+--------------+
| 2016-12-25 | column value |
+------------+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Description

Returns the value corresponding to the specified date part. The part must be one of:

  • DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day of the week.
  • DAY
  • DAYOFYEAR
  • WEEK: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.
  • WEEK(<WEEKDAY>): Returns the week number of the date in the range [0, 53]. Weeks begin on WEEKDAY. Dates prior to the first WEEKDAY of the year are in week 0. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  • ISOWEEK: Returns the ISO 8601 week number of the date_expression. ISOWEEKs begin on Monday. Return values are in the range [1, 53]. The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.
  • MONTH
  • QUARTER: Returns values in the range [1,4].
  • YEAR
  • ISOYEAR: Returns the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date_expression belongs.

Return Data Type

INT64

Examples

In the following example, EXTRACT returns a value corresponding to the DAY date part.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') AS the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

In the following example, EXTRACT returns values corresponding to different date parts from a column of dates near the end of the year.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

In the following example, date_expression falls on a Sunday. EXTRACT calculates the first column using weeks that begin on Sunday, and it calculates the second column using weeks that begin on Monday.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

DATE(year, month, day)
DATE(timestamp_expression)
DATE(timestamp_expression, time_zone_expression)
DATE(datetime_expression)

Description

Constructs or extracts a date.

This function supports the following arguments:

  • year: The INT64 value for year.
  • month: The INT64 value for month.
  • day: The INT64 value for day.
  • timestamp_expression: A TIMESTAMP expression that contains the date.
  • time_zone_expression: A STRING expression that represents a time zone. If no time zone is specified with timestamp_expression, the default time zone, which is implementation defined, is used.
  • datetime_expression: A DATETIME expression that contains the date.

Return Data Type

DATE

Example

SELECT
  DATE(2016, 12, 25) AS date_ymd,
  DATE(DATETIME '2016-12-25 23:59:59') AS date_dt,
  DATE(TIMESTAMP '2016-12-25 05:30:00+07', 'America/Los_Angeles') AS date_tstz;

+------------+------------+------------+
| date_ymd   | date_dt    | date_tstz  |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Description

Adds a specified time interval to a DATE.

DATE_ADD supports the following date_part values:

  • DAY
  • WEEK. Equivalent to 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the resulting date is the last date of that month.

Return Data Type

DATE

Example

SELECT DATE_ADD(DATE '2008-12-25', INTERVAL 5 DAY) AS five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

Description

Subtracts a specified time interval from a DATE.

DATE_SUB supports the following date_part values:

  • DAY
  • WEEK. Equivalent to 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the resulting date is the last date of that month.

Return Data Type

DATE

Example

SELECT DATE_SUB(DATE '2008-12-25', INTERVAL 5 DAY) AS five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Description

Returns the whole number of specified date_part intervals between two DATE objects (date_expression_a - date_expression_b). If the first DATE is earlier than the second one, the output is negative.

DATE_DIFF supports the following date_part values:

  • DAY
  • WEEK This date part begins on Sunday.
  • WEEK(<WEEKDAY>): This date part begins on WEEKDAY. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  • ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.
  • MONTH, except when the first two arguments are TIMESTAMP objects.
  • QUARTER
  • YEAR
  • ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Return Data Type

INT64

Example

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) AS days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) AS days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) AS weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

The example above shows the result of DATE_DIFF for two days in succession. DATE_DIFF with the date part WEEK returns 1 because DATE_DIFF counts the number of date part boundaries in this range of dates. Each WEEK begins on Sunday, so there is one date part boundary between Saturday, 2017-10-14 and Sunday, 2017-10-15.

The following example shows the result of DATE_DIFF for two dates in different years. DATE_DIFF with the date part YEAR returns 3 because it counts the number of Gregorian calendar year boundaries between the two dates. DATE_DIFF with the date part ISOYEAR returns 2 because the second date belongs to the ISO year 2015. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

The following example shows the result of DATE_DIFF for two days in succession. The first date falls on a Monday and the second date falls on a Sunday. DATE_DIFF with the date part WEEK returns 0 because this date part uses weeks that begin on Sunday. DATE_DIFF with the date part WEEK(MONDAY) returns 1. DATE_DIFF with the date part ISOWEEK also returns 1 because ISO weeks begin on Monday.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Description

Truncates a DATE value to the granularity of date_part. The DATE value is always rounded to the beginning of date_part, which can be one of the following:

  • DAY: The day in the Gregorian calendar year that contains the DATE value.
  • WEEK: The first day of the week in the week that contains the DATE 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 DATE 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 in the ISO week that contains the DATE 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 DATE value.
  • QUARTER: The first day of the quarter in the quarter that contains the DATE value.
  • YEAR: The first day of the year in the year that contains the DATE value.
  • ISOYEAR: The first day of the ISO 8601 week-numbering year in the ISO year that contains the DATE value. The ISO year is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Return Data Type

DATE

Examples

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) AS month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

In the following example, the original date falls on a Sunday. Because the date_part is WEEK(MONDAY), DATE_TRUNC returns the DATE for the preceding Monday.

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

In the following example, the original date_expression is in the Gregorian calendar year 2015. However, DATE_TRUNC with the ISOYEAR date part truncates the date_expression to the beginning of the ISO year, not the Gregorian calendar year. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29. Therefore the ISO year boundary preceding the date_expression 2015-06-15 is 2014-12-29.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

Description

Interprets int64_expression as the number of days since 1970-01-01.

Return Data Type

DATE

Example

SELECT DATE_FROM_UNIX_DATE(14238) AS date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Description

Formats the date_expr according to the specified format_string.

See Supported Format Elements For DATE for a list of format elements that this function supports.

Return Data Type

STRING

Examples

SELECT FORMAT_DATE('%x', DATE '2008-12-25') AS US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE('%b-%d-%Y', DATE '2008-12-25') AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE('%b %Y', DATE '2008-12-25') AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

LAST_DAY

LAST_DAY(date_expression[, date_part])

Description

Returns the last day from a date expression. This is commonly used to return the last day of the month.

You can optionally specify the date part for which the last day is returned. If this parameter is not used, the default value is MONTH. LAST_DAY supports the following values for date_part:

  • YEAR
  • QUARTER
  • MONTH
  • WEEK. Equivalent to 7 DAYs.
  • WEEK(<WEEKDAY>). <WEEKDAY> represents the starting day of the week. Valid values are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  • ISOWEEK. Uses ISO 8601 week boundaries. ISO weeks begin on Monday.
  • ISOYEAR. Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Return Data Type

DATE

Example

These both return the last day of the month:

SELECT LAST_DAY(DATE '2008-11-25', MONTH) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+
SELECT LAST_DAY(DATE '2008-11-25') AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+

This returns the last day of the year:

SELECT LAST_DAY(DATE '2008-11-25', YEAR) AS last_day

+------------+
| last_day   |
+------------+
| 2008-12-31 |
+------------+

This returns the last day of the week for a week that starts on a Sunday:

SELECT LAST_DAY(DATE '2008-11-10', WEEK(SUNDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-15 |
+------------+

This returns the last day of the week for a week that starts on a Monday:

SELECT LAST_DAY(DATE '2008-11-10', WEEK(MONDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-16 |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Description

Converts a string representation of date to a DATE object.

format_string contains the format elements that define how date_string is formatted. Each element in date_string must have a corresponding element in format_string. The location of each element in format_string must match the location of each element in date_string.

-- This works because elements on both sides match.
SELECT PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008')

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE('%Y %A %b %e', 'Thursday Dec 25 2008')

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE('%A %b %e', 'Thursday Dec 25 2008')

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE('%F', '2000-12-30')

When using PARSE_DATE, keep the following in mind:

  • Unspecified fields. Any unspecified field is initialized from 1970-01-01.
  • Case insensitivity. Names, such as Monday, February, and so on, are case insensitive.
  • Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the date string. In addition, leading and trailing white spaces in the date string are always allowed -- even if they are not in the format string.
  • Format precedence. When two (or more) format elements have overlapping information (for example both %F and %Y affect the year), the last one generally overrides any earlier ones.

Return Data Type

DATE

Examples

This example converts a MM/DD/YY formatted string to a DATE object:

SELECT PARSE_DATE('%x', '12/25/08') AS parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

This example converts a YYYYMMDD formatted string to a DATE object:

SELECT PARSE_DATE('%Y%m%d', '20081225') AS parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Description

Returns the number of days since 1970-01-01.

Return Data Type

INT64

Example

SELECT UNIX_DATE(DATE '2008-12-25') AS days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+