GoogleSQL for BigQuery supports the following date functions.
Function list
Name | Summary |
---|---|
CURRENT_DATE
|
Returns the current date as a DATE value.
|
DATE
|
Constructs a DATE value.
|
DATE_ADD
|
Adds a specified time interval to a DATE value.
|
DATE_DIFF
|
Gets the number of intervals between two DATE values.
|
DATE_FROM_UNIX_DATE
|
Interprets an INT64 expression as the number of days
since 1970-01-01.
|
DATE_SUB
|
Subtracts a specified time interval from a DATE value.
|
DATE_TRUNC
|
Truncates a DATE value.
|
EXTRACT
|
Extracts part of a date from a DATE value.
|
FORMAT_DATE
|
Formats a DATE value according to a specified format string.
|
LAST_DAY
|
Gets the last day in a specified time period that contains a
DATE value.
|
PARSE_DATE
|
Converts a STRING value to a DATE value.
|
UNIX_DATE
|
Converts a DATE value to the number of days since 1970-01-01.
|
CURRENT_DATE
CURRENT_DATE()
CURRENT_DATE(time_zone_expression)
CURRENT_DATE
Description
Returns the current date as a DATE
object. Parentheses are optional when
called with no arguments.
This function supports the following arguments:
time_zone_expression
: ASTRING
expression that represents a time zone. If no time zone is specified, the default time zone, UTC, is used. If this expression is used and it evaluates toNULL
, this function returnsNULL
.
The current date is recorded at the start of the query statement which contains this function, not when this specific function is evaluated.
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 |
*------------+--------------*/
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
: TheINT64
value for year.month
: TheINT64
value for month.day
: TheINT64
value for day.timestamp_expression
: ATIMESTAMP
expression that contains the date.time_zone_expression
: ASTRING
expression that represents a time zone. If no time zone is specified withtimestamp_expression
, the default time zone, UTC, is used.datetime_expression
: ADATETIME
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 7DAY
s.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_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 onWEEKDAY
. Valid values forWEEKDAY
areSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, andSATURDAY
.ISOWEEK
: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.MONTH
, except when the first two arguments areTIMESTAMP
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_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 |
*-----------------+*/
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 7DAY
s.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_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 beginn