GoogleSQL for BigQuery supports the following datetime functions.
All outputs are automatically formatted as per ISO 8601,
separating date and time with aT
.
Function list
Name | Summary |
---|---|
CURRENT_DATETIME
|
Returns the current date and time as a DATETIME value.
|
DATETIME
|
Constructs a DATETIME value.
|
DATETIME_ADD
|
Adds a specified time interval to a DATETIME value.
|
DATETIME_DIFF
|
Gets the number of unit boundaries between two DATETIME values
at a particular time granularity.
|
DATETIME_SUB
|
Subtracts a specified time interval from a DATETIME value.
|
DATETIME_TRUNC
|
Truncates a DATETIME or
TIMESTAMP value at a particular
granularity.
|
EXTRACT
|
Extracts part of a date and time from a DATETIME value.
|
FORMAT_DATETIME
|
Formats a DATETIME value according to a specified
format string.
|
LAST_DAY
|
Gets the last day in a specified time period that contains a
DATETIME value.
|
PARSE_DATETIME
|
Converts a STRING value to a DATETIME value.
|
CURRENT_DATETIME
CURRENT_DATETIME([time_zone])
CURRENT_DATETIME
Description
Returns the current time as a DATETIME
object. Parentheses are optional when
called with no arguments.
This function supports an optional time_zone
parameter.
See Time zone definitions for
information on how to specify a time zone.
The current date and time is recorded at the start of the query statement which contains this function, not when this specific function is evaluated.
Return Data Type
DATETIME
Example
SELECT CURRENT_DATETIME() as now;
/*----------------------------*
| now |
+----------------------------+
| 2016-05-19T10:38:47.046465 |
*----------------------------*/
DATETIME
1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression[, time_expression])
3. DATETIME(timestamp_expression [, time_zone])
Description
- Constructs a
DATETIME
object usingINT64
values representing the year, month, day, hour, minute, and second. - Constructs a
DATETIME
object using a DATE object and an optionalTIME
object. - Constructs a
DATETIME
object using aTIMESTAMP
object. It supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used.
Return Data Type
DATETIME
Example
SELECT
DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;
/*---------------------+---------------------*
| datetime_ymdhms | datetime_tstz |
+---------------------+---------------------+
| 2008-12-25T05:30:00 | 2008-12-24T21:30:00 |
*---------------------+---------------------*/
DATETIME_ADD
DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)
Description
Adds int64_expression
units of part
to the DATETIME
object.
DATETIME_ADD
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
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 DATETIME's day, then the result day is the last day of the new month.
Return Data Type
DATETIME
Example
SELECT
DATETIME "2008-12-25 15:30:00" as original_date,
DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;
/*-----------------------------+------------------------*
| original_date | later |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T15:40:00 |
*-----------------------------+------------------------*/
DATETIME_DIFF
DATETIME_DIFF(end_datetime, start_datetime, granularity)
Description
Gets the number of unit boundaries between two DATETIME
values
(end_datetime
- start_datetime
) at a particular time granularity.
Definitions
start_datetime
: The startingDATETIME
value.end_datetime
: The endingDATETIME
value.granularity
: The datetime part that represents the granularity. If you have passed inDATETIME
values for the first arguments,granularity
can be:MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
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_datetime
is earlier than start_datetime
, the output is negative.
Produces an error if the computation overflows, such as if the difference
in microseconds
between the two DATETIME
values overflows.
Return Data Type
INT64
Example
SELECT
DATETIME "2010-07-07 10:20:00" as first_datetime,
DATETIME "2008-12-25 15:30:00" as second_datetime,
DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
DATETIME "2008-12-25 15:30:00", DAY) as difference;
/*----------------------------+------------------------+------------------------*
| first_datetime | second_datetime | difference |
+----------------------------+------------------------+------------------------+
| 2010-07-07T10:20:00 | 2008-12-25T15:30:00 | 559 |
*----------------------------+------------------------+------------------------*/
SELECT
DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;
/*-----------+------------*
| days_diff | weeks_diff |
+-----------+------------+
| 1 | 1 |
*-----------+------------*/
The example above shows the result of DATETIME_DIFF
for two DATETIME
s that
are 24 hours apart. DATETIME_DIFF
with the part WEEK
returns 1 because
DATETIME_DIFF
counts the number of part boundaries in this range of
DATETIME
s. Each WEEK
begins on Sunday, so there is one part boundary between
Saturday, 2017-10-14 00:00:00
and Sunday, 2017-10-15 00:00:00
.
The following example shows the result of DATETIME_DIFF
for two dates in
different years. DATETIME_DIFF
with the date part YEAR
returns 3 because it
counts the number of Gregorian calendar year boundaries between the two
DATETIME
s. DATETIME_DIFF
with the date part ISOYEAR
returns 2 because the
second DATETIME
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
DATETIME_DIFF('2017-12-30 00:00:00',
'2014-12-30 00:00:00', YEAR) AS year_diff,
DATETIME_DIFF('2017-12-30 00:00:00',
'2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;
/*-----------+--------------*
| year_diff | isoyear_diff |
+-----------+--------------+
| 3 | 2 |
*-----------+--------------*/
The following example shows the result of DATETIME_DIFF
for two days in
succession. The first date falls on a Monday and the second date falls on a
Sunday. DATETIME_DIFF
with the date part WEEK
returns 0 because this time
part uses weeks that begin on Sunday. DATETIME_DIFF
with the date part
WEEK(MONDAY)
returns 1. DATETIME_DIFF
with the date part
ISOWEEK
also returns 1 because ISO weeks begin on Monday.
SELECT
DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;
/*-----------+-------------------+--------------*
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0 | 1 | 1 |
*-----------+-------------------+--------------*/
DATETIME_SUB
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
Description
Subtracts int64_expression
units of part
from the DATETIME
.
DATETIME_SUB
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
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 DATETIME
's day, then the result day is the last day of
the new month.
Return Data Type
DATETIME
Example
SELECT
DATETIME "2008-12-25 15:30:00" as original_date,
DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;
/*-----------------------------+------------------------*
| original_date | earlier |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T15:20:00 |
*-----------------------------+------------------------*/
DATETIME_TRUNC
DATETIME_TRUNC(datetime_value, datetime_granularity)
DATETIME_TRUNC(timestamp_value, timestamp_granularity[, time_zone])
Description
Truncates a DATETIME
or TIMESTAMP
value at a particular granularity.
Definitions
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
DATETIME "2008-12-25 15:30:00" as original,
DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;
/*----------------------------+------------------------*
| original | truncated |
+----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T00:00:00 |
*----------------------------+------------------------*/
In the following example, the original DATETIME
falls on a Sunday. Because the
part
is WEEK(MONDAY)
, DATE_TRUNC
returns the DATETIME
for the
preceding Monday.
SELECT
datetime AS original,
DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime);
/*---------------------+---------------------*
| original | truncated |
+---------------------+---------------------+
| 2017-11-05T00:00:00 | 2017-10-30T00:00:00 |
*---------------------+---------------------*/
In the following example, the original datetime_expression
is in the Gregorian
calendar year 2015. However, DATETIME_TRUNC
with the ISOYEAR
date part
truncates the datetime_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 datetime_expression
2015-06-15 00:00:00 is 2014-12-29.
SELECT
DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;
/*---------------------+----------------*
| isoyear_boundary | isoyear_number |
+---------------------+----------------+
| 2014-12-29T00:00:00 | 2015 |
*---------------------+----------------*/
EXTRACT
EXTRACT(part FROM datetime_expression)
Description
Returns a value that corresponds to the
specified part
from a supplied datetime_expression
.
Allowed part
values are:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAYOFWEEK
: Returns values in the range [1,7] with Sunday as the first day of 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 ofdatetime_expression
in the range [0, 53]. Weeks begin onWEEKDAY
.datetime
s 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 thedatetime_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
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.DATE
TIME
Returned values truncate lower order time periods. For example, when extracting
seconds, EXTRACT
truncates the millisecond and microsecond values.
Return Data Type
INT64
, except in the following cases:
- If
part
isDATE
, returns aDATE
object. - If
part
isTIME
, returns aTIME
object.
Examples
In the following example, EXTRACT
returns a value corresponding to the HOUR
time part.
SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;
/*------------------*
| hour |
+------------------+
| 15 |
*------------------*/
In the following example, EXTRACT
returns values corresponding to different
time parts from a column of datetimes.
WITH Datetimes AS (
SELECT DATETIME '2005-01-03 12:34:56' AS datetime UNION ALL
SELECT DATETIME '2007-12-31' UNION ALL
SELECT DATETIME '2009-01-01' UNION ALL
SELECT DATETIME '2009-12-31' UNION ALL
SELECT DATETIME '2017-01-02' UNION ALL
SELECT DATETIME '2017-05-26'
)
SELECT
datetime,
EXTRACT(ISOYEAR FROM datetime) AS isoyear,
EXTRACT(ISOWEEK FROM datetime) AS isoweek,
EXTRACT(YEAR FROM datetime) AS year,
EXTRACT(WEEK FROM datetime) AS week
FROM Datetimes
ORDER BY datetime;
/*---------------------+---------+---------+------+------*
| datetime | isoyear | isoweek | year | week |
+---------------------+---------+---------+------+------+
| 2005-01-03T12:34:56 | 2005 | 1 | 2005 | 1 |
| 2007-12-31T00:00:00 | 2008 | 1 | 2007 | 52 |
| 2009-01-01T00:00:00 | 2009 | 1 | 2009 | 0 |
| 2009-12-31T00:00:00 | 2009 | 53 | 2009 | 52 |
| 2017-01-02T00:00:00 | 2017 | 1 | 2017 | 1 |
| 2017-05-26T00:00:00 | 2017 | 21 | 2017 | 21 |
*---------------------+---------+---------+------+------*/
In the following example, datetime_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 DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime)
SELECT
datetime,
EXTRACT(WEEK(SUNDAY) FROM datetime) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM datetime) AS week_monday
FROM table;
/*---------------------+-------------+---------------*
| datetime | week_sunday | week_monday |
+---------------------+-------------+---------------+
| 2017-11-05T00:00:00 | 45 | 44 |
*---------------------+-------------+---------------*/
FORMAT_DATETIME
FORMAT_DATETIME(format_string, datetime_expr)
Description
Formats a DATETIME
value according to a specified format string.
Definitions
format_string
: ASTRING
value that contains the format elements to use withdatetime_expr
.datetime_expr
: ADATETIME
value that represents the date and time to format.
Return Data Type
STRING
Examples
SELECT
FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
AS formatted;
/*--------------------------*
| formatted |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
*--------------------------*/
SELECT
FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
/*-------------*
| formatted |
+-------------+
| Dec-25-2008 |
*-------------*/
SELECT
FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
/*-------------*
| formatted |
+-------------+
| Dec 2008 |
*-------------*/
LAST_DAY
LAST_DAY(datetime_expression[, date_part])
Description
Returns the last day from a datetime expression that contains the date. 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(DATETIME '2008-11-25', MONTH) AS last_day
/*------------*
| last_day |
+------------+
| 2008-11-30 |
*------------*/
SELECT LAST_DAY(DATETIME '2008-11-25') AS last_day
/*------------*
| last_day |
+------------+
| 2008-11-30 |
*------------*/
This returns the last day of the year:
SELECT LAST_DAY(DATETIME '2008-11-25 15:30:00', 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(DATETIME '2008-11-10 15:30:00', 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(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day
/*------------*
| last_day |
+------------+
| 2008-11-16 |
*------------*/
PARSE_DATETIME
PARSE_DATETIME(format_string, datetime_string)
Description
Converts a STRING
value to a DATETIME
value.
Definitions
format_string
: ASTRING
value that contains the format elements to use withdatetime_string
.datetime_string
: ASTRING
value that represents the date and time to parse.
Details
Each element in datetime_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 datetime_string
.
-- This works because elements on both sides match.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");
-- This produces an error because the year element is in different locations.
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");
-- This produces an error because one of the year elements is missing.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");
-- This works because %c can find all matching elements in datetime_string.
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008");
The format string fully supports most format elements, except for
%P
.
PARSE_DATETIME
parses string
according to the following rules:
- Unspecified fields. Any unspecified field is initialized from
1970-01-01 00:00:00.0
. For example, if the year is unspecified then it defaults to1970
. - Case insensitivity. Names, such as
Monday
andFebruary
, are case insensitive. - Whitespace. One or more consecutive white spaces in the format string
matches zero or more consecutive white spaces in the
DATETIME
string. Leading and trailing white spaces in theDATETIME
string are always allowed, even if they are not in the format string. - Format precedence. When two or more format elements have overlapping
information, the last one generally overrides any earlier ones, with some
exceptions. For example, both
%F
and%Y
affect the year, so the earlier element overrides the later. See the descriptions of%s
,%C
, and%y
in Supported Format Elements For DATETIME. - Format divergence.
%p
can be used witham
,AM
,pm
, andPM
.
Return Data Type
DATETIME
Examples
The following examples parse a STRING
literal as a
DATETIME
.
SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;
/*---------------------*
| datetime |
+---------------------+
| 1998-10-18T13:45:55 |
*---------------------*/
SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') AS datetime;
/*---------------------*
| datetime |
+---------------------+
| 2018-08-30T14:23:38 |
*---------------------*/
The following example parses a STRING
literal
containing a date in a natural language format as a
DATETIME
.
SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
AS datetime;
/*---------------------*
| datetime |
+---------------------+
| 2018-12-19T00:00:00 |
*---------------------*/