GoogleSQL for Bigtable 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 unit boundaries between two DATE values
at a particular time granularity.
|
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 , DATETIME , or
TIMESTAMP value at a particular
granularity.
|
EXTRACT
|
Extracts part of a date from a DATE value.
|
FORMAT_DATE
|
Formats a DATE value according to a specified format string.
|
GENERATE_DATE_ARRAY
|
Generates an array of dates in a range.
For more information, see Array functions. |
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 |
*--------------*/
DATE
DATE(year, month, day)
DATE(timestamp_expression)
DATE(timestamp_expression, time_zone_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.
Return Data Type
DATE
Example
SELECT
DATE(2016, 12, 25) AS date_ymd,
DATE(TIMESTAMP '2016-12-25 05:30:00+07', 'America/Los_Angeles') AS date_tstz;
/*------------+------------*
| date_ymd | date_tstz |
+------------+------------+
| 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(end_date, start_date, granularity)
Description
Gets the number of unit boundaries between two DATE
values (end_date
-
start_date
) at a particular time granularity.
Definitions
start_date
: The startingDATE
value.end_date
: The endingDATE
value.granularity
: The date part that represents the granularity. This can be: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
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.
Details
If end_date
is earlier than start_date
, the output is negative.
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_value, date_granularity)
DATE_TRUNC(datetime_value, datetime_granularity)
DATE_TRUNC(timestamp_value, timestamp_granularity[, time_zone])
Description
Truncates a DATE
, DATETIME
, or TIMESTAMP
value at a particular
granularity.
Definitions
date_value
: ADATE
value to truncate.date_granularity
: The truncation granularity for aDATE
value. Date granularities can be used.datetime_value
: ADATETIME
value to truncate.datetime_granularity
: The truncation granularity for aDATETIME
value. Date granularities and time granularities can be used.timestamp_value
: ATIMESTAMP
value to truncate.timestamp_granularity
: The truncation granularity for aTIMESTAMP
value. Date granularities and time granularities can be used.time_zone
: A time zone to use with theTIMESTAMP
value. Time zone parts can be used. Use this argument if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation.
Date granularity definitions
DAY
: The day in the Gregorian calendar year that contains the value to truncate.WEEK
: The first day in the week that contains the value to truncate. Weeks begin on Sundays.WEEK
is equivalent toWEEK(SUNDAY)
.WEEK(WEEKDAY)
: The first day in the week that contains the value to truncate. Weeks begin onWEEKDAY
.WEEKDAY
must be one of the following:SUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, orSATURDAY
.ISOWEEK
: The first day in the ISO 8601 week that contains the value to truncate. 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 in the month that contains the value to truncate.QUARTER
: The first day in the quarter that contains the value to truncate.YEAR
: The first day in the year that contains the value to truncate.ISOYEAR
: The first day in the ISO 8601 week-numbering year that contains the value to truncate. The ISO year is the Monday of the first week where Thursday belongs to the corresponding Gregorian calendar year.
Time granularity definitions
MICROSECOND
: If used, nothing is truncated from the value.MILLISECOND
: The nearest lesser than or equal millisecond.SECOND
: The nearest lesser than or equal second.MINUTE
: The nearest lesser than or equal minute.HOUR
: The nearest lesser than or equal hour.
Time zone part definitions
MINUTE
HOUR
DAY
WEEK
WEEK(<WEEKDAY>)
ISOWEEK
MONTH
QUARTER
YEAR
ISOYEAR
Details
The resulting value is always rounded to the beginning of granularity
.
Return Data Type
The same data type as the first argument passed into this function.
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 |
*------------------+----------------*/
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 onWEEKDAY
. Dates prior to the firstWEEKDAY
of the year are in week 0. Valid values forWEEKDAY
areSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, andSATURDAY
.ISOWEEK
: Returns the ISO 8601 week number of thedate_expression
.ISOWEEK
s begin on Monday. Return values are in the range [1, 53]. The firstISOWEEK
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 whichdate_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.
FORMAT_DATE
FORMAT_DATE(format_string, date_expr)
Description
Formats a DATE
value according to a specified format string.
Definitions
format_string
: ASTRING
value that contains the format elements to use withdate_expr
.date_expr
: ADATE
value that represents the date to format.
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 7DAY
s.WEEK(<WEEKDAY>)
.<WEEKDAY>
represents the starting day of the week. Valid values areSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, andSATURDAY
.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
value to a DATE
value.
Definitions
format_string
: ASTRING
value that contains the format elements to use withdate_string
.date_string
: ASTRING
value that represents the date to parse.
Details
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 produces an error because the year element is in different locations.
SELECT PARSE_DATE('%Y %A %b %e', 'Thursday Dec 25 2008');
-- This produces an error 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 |
*-----------------*/