GoogleSQL for BigQuery supports the following datetime functions.

All outputs are automatically formatted as perISO 8601,separating date and time with aT.

Function list

CURRENT_DATETIME

CURRENT_DATETIME([time_zone])

CURRENT_DATETIME

Description

Returns the current time as aDATETIME object. Parentheses are optional whencalled with no arguments.

This function supports an optionaltime_zone parameter.SeeTime zone definitions forinformation on how to specify a time zone.

The current date and time value is set at the start of the query statement thatcontains this function. All invocations ofCURRENT_DATETIME() within a querystatement yield the same value.

Return Data Type

DATETIME

Example

SELECTCURRENT_DATETIME()asnow;/*----------------------------+ | 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

1. Constructs aDATETIME object usingINT64 valuesrepresenting the year, month, day, hour, minute, and second.
2. Constructs aDATETIME object using a DATE object and an optionalTIMEobject.
3. Constructs aDATETIME object using aTIMESTAMP object. It supports anoptional parameter tospecify a time zone.If no time zone is specified, the default time zone, UTC,is used.

Return Data Type

DATETIME

Example

SELECTDATETIME(2008,12,25,05,30,00)asdatetime_ymdhms,DATETIME(TIMESTAMP"2008-12-25 05:30:00+00","America/Los_Angeles")asdatetime_tstz;/*---------------------+---------------------+ | datetime_ymdhms     | datetime_tstz       | +---------------------+---------------------+ | 2008-12-25T05:30:00 | 2008-12-24T21:30:00 | +---------------------+---------------------*/

DATETIME_ADD

DATETIME_ADD(datetime_expression,INTERVALint64_expressionpart)

Description

Addsint64_expression units ofpart to theDATETIME object.

DATETIME_ADD supports the following values forpart:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalent to 7DAYs.
• MONTH
• QUARTER
• YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when thedate is at (or near) the last day of the month. If the resulting month has fewerdays than the original DATETIME's day, then the result day is the last day ofthe new month.

Return Data Type

DATETIME

Example

SELECTDATETIME"2008-12-25 15:30:00"asoriginal_date,DATETIME_ADD(DATETIME"2008-12-25 15:30:00",INTERVAL10MINUTE)aslater;/*-----------------------------+------------------------+ | 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 twoDATETIME 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. Ifyou have passed inDATETIME values for the first arguments,granularitycan 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: UsesISO 8601 weekboundaries. ISO weeks begin on Monday.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Uses theISO 8601week-numbering year boundary. The ISO year boundary is the Monday of thefirst week whose Thursday belongs to the corresponding Gregorian calendaryear.

Details

Ifend_datetime is earlier thanstart_datetime, the output is negative.Produces an error if the computation overflows, such as if the differencein microsecondsbetween the twoDATETIME values overflows.

SELECTDATETIME"2010-07-07 10:20:00"asfirst_datetime,DATETIME"2008-12-25 15:30:00"assecond_datetime,DATETIME_DIFF(DATETIME"2010-07-07 10:20:00",DATETIME"2008-12-25 15:30:00",DAY)asdifference;/*----------------------------+------------------------+------------------------+ | first_datetime             | second_datetime        | difference             | +----------------------------+------------------------+------------------------+ | 2010-07-07T10:20:00        | 2008-12-25T15:30:00    | 559                    | +----------------------------+------------------------+------------------------*/

SELECTDATETIME_DIFF(DATETIME'2017-10-15 00:00:00',DATETIME'2017-10-14 00:00:00',DAY)asdays_diff,DATETIME_DIFF(DATETIME'2017-10-15 00:00:00',DATETIME'2017-10-14 00:00:00',WEEK)asweeks_diff;/*-----------+------------+ | days_diff | weeks_diff | +-----------+------------+ | 1         | 1          | +-----------+------------*/

SELECTDATETIME_DIFF('2017-12-30 00:00:00','2014-12-30 00:00:00',YEAR)ASyear_diff,DATETIME_DIFF('2017-12-30 00:00:00','2014-12-30 00:00:00',ISOYEAR)ASisoyear_diff;/*-----------+--------------+ | year_diff | isoyear_diff | +-----------+--------------+ | 3         | 2            | +-----------+--------------*/

SELECTDATETIME_DIFF('2017-12-18','2017-12-17',WEEK)ASweek_diff,DATETIME_DIFF('2017-12-18','2017-12-17',WEEK(MONDAY))ASweek_weekday_diff,DATETIME_DIFF('2017-12-18','2017-12-17',ISOWEEK)ASisoweek_diff;/*-----------+-------------------+--------------+ | week_diff | week_weekday_diff | isoweek_diff | +-----------+-------------------+--------------+ | 0         | 1                 | 1            | +-----------+-------------------+--------------*/

DATETIME_SUB(datetime_expression,INTERVALint64_expressionpart)

SELECTDATETIME"2008-12-25 15:30:00"asoriginal_date,DATETIME_SUB(DATETIME"2008-12-25 15:30:00",INTERVAL10MINUTE)asearlier;/*-----------------------------+------------------------+ | original_date               | earlier                | +-----------------------------+------------------------+ | 2008-12-25T15:30:00         | 2008-12-25T15:20:00    | +-----------------------------+------------------------*/

DATETIME_TRUNC(datetime_value,datetime_granularity)

DATETIME_TRUNC(timestamp_value,timestamp_granularity[,time_zone])

• datetime_value: ADATETIME value to truncate.
• datetime_granularity: The truncation granularity for aDATETIME value.Date granularities andtime granularities can be used.
• timestamp_value: ATIMESTAMP value to truncate.
• timestamp_granularity: The truncation granularity for aTIMESTAMP value.Date granularities andtime 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 thanthe default time zone, UTC, as part of thetruncate operation.

  Note: When truncating a timestamp toMINUTEorHOUR parts, this function determines the civil time of thetimestamp in the specified (or default) time zoneand subtracts the minutes and seconds (when truncating toHOUR) or theseconds (when truncating toMINUTE) from that timestamp.While this provides intuitive results in most cases, the result isnon-intuitive near daylight savings transitions that aren't hour-aligned.

Date granularity definitions

• DAY: The day in the Gregorian calendar year that contains thevalue to truncate.

• WEEK: The first day in the week that contains thevalue to truncate. Weeks begin on Sundays.WEEK is equivalent toWEEK(SUNDAY).

• WEEK(WEEKDAY): The first day in the week that contains thevalue to truncate. Weeks begin onWEEKDAY.WEEKDAY must be one of the following:SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, orSATURDAY.

• ISOWEEK: The first day in theISO 8601 week that containsthe value to truncate. The ISO week begins onMonday. The first ISO week of each ISO year contains the first Thursday of thecorresponding Gregorian calendar year.

• MONTH: The first day in the month that contains thevalue to truncate.

• QUARTER: The first day in the quarter that contains thevalue to truncate.

• YEAR: The first day in the year that contains thevalue to truncate.

• ISOYEAR: The first day in theISO 8601 week-numbering yearthat contains the value to truncate. The ISO year is theMonday of the first week where Thursday belongs to the correspondingGregorian 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 ofgranularity.

Return Data Type

The same data type as the first argument passed into this function.

Examples

SELECTDATETIME"2008-12-25 15:30:00"asoriginal,DATETIME_TRUNC(DATETIME"2008-12-25 15:30:00",DAY)astruncated;/*----------------------------+------------------------+ | original                   | truncated              | +----------------------------+------------------------+ | 2008-12-25T15:30:00        | 2008-12-25T00:00:00    | +----------------------------+------------------------*/

In the following example, the originalDATETIME falls on a Sunday. Because thepart isWEEK(MONDAY),DATE_TRUNC returns theDATETIME for thepreceding Monday.

SELECTdatetimeASoriginal,DATETIME_TRUNC(datetime,WEEK(MONDAY))AStruncatedFROM(SELECTDATETIME(TIMESTAMP"2017-11-05 00:00:00+00","UTC")ASdatetime);/*---------------------+---------------------+ | original            | truncated           | +---------------------+---------------------+ | 2017-11-05T00:00:00 | 2017-10-30T00:00:00 | +---------------------+---------------------*/

In the following example, the originaldatetime_expression is in the Gregoriancalendar year 2015. However,DATETIME_TRUNC with theISOYEAR date parttruncates thedatetime_expression to the beginning of the ISO year, not theGregorian calendar year. The first Thursday of the 2015 calendar year was2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29.Therefore the ISO year boundary preceding thedatetime_expression2015-06-15 00:00:00 is 2014-12-29.

SELECTDATETIME_TRUNC('2015-06-15 00:00:00',ISOYEAR)ASisoyear_boundary,EXTRACT(ISOYEARFROMDATETIME'2015-06-15 00:00:00')ASisoyear_number;/*---------------------+----------------+ | isoyear_boundary    | isoyear_number | +---------------------+----------------+ | 2014-12-29T00:00:00 | 2015           | +---------------------+----------------*/

EXTRACT

EXTRACT(partFROMdatetime_expression)

Description

Returns a value that corresponds to thespecifiedpart from a supplieddatetime_expression.

Allowedpart values are:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day ofof the week.
• DAY
• DAYOFYEAR
• WEEK: Returns the week number of the date in the range [0, 53]. Weeks beginwith Sunday, and dates prior to the first Sunday of the year are in week0.
• WEEK(<WEEKDAY>): Returns the week number ofdatetime_expression in therange [0, 53]. Weeks begin onWEEKDAY.datetimes prior to the firstWEEKDAY of the year are in week 0. Validvalues forWEEKDAY areSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.
• ISOWEEK: Returns theISO 8601 weeknumber of thedatetime_expression.ISOWEEKs begin on Monday. Return valuesare in the range [1, 53]. The firstISOWEEK of each ISO year begins on theMonday before the first Thursday of the Gregorian calendar year.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Returns theISO 8601week-numbering year, which is the Gregorian calendar year containing theThursday of the week to whichdate_expression belongs.
• DATE
• TIME

Returned values truncate lower order time periods. For example, when extractingseconds,EXTRACT truncates the millisecond and microsecond values.

Return Data Type

INT64, except in the following cases:

• Ifpart isDATE, returns aDATE object.
• Ifpart isTIME, returns aTIME object.

Examples

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

SELECTEXTRACT(HOURFROMDATETIME(2008,12,25,15,30,00))ashour;/*------------------+ | hour             | +------------------+ | 15               | +------------------*/

In the following example,EXTRACT returns values corresponding to differenttime parts from a column of datetimes.

WITHDatetimesAS(SELECTDATETIME'2005-01-03 12:34:56'ASdatetimeUNIONALLSELECTDATETIME'2007-12-31'UNIONALLSELECTDATETIME'2009-01-01'UNIONALLSELECTDATETIME'2009-12-31'UNIONALLSELECTDATETIME'2017-01-02'UNIONALLSELECTDATETIME'2017-05-26')SELECTdatetime,EXTRACT(ISOYEARFROMdatetime)ASisoyear,EXTRACT(ISOWEEK FROMdatetime)ASisoweek,EXTRACT(YEARFROMdatetime)ASyear,EXTRACT(WEEKFROMdatetime)ASweekFROMDatetimesORDERBYdatetime;/*---------------------+---------+---------+------+------+ | 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.EXTRACTcalculates the first column using weeks that begin on Sunday, and it calculatesthe second column using weeks that begin on Monday.

WITHtableAS(SELECTDATETIME(TIMESTAMP"2017-11-05 00:00:00+00","UTC")ASdatetime)SELECTdatetime,EXTRACT(WEEK(SUNDAY)FROMdatetime)ASweek_sunday,EXTRACT(WEEK(MONDAY)FROMdatetime)ASweek_mondayFROMtable;/*---------------------+-------------+---------------+ | datetime            | week_sunday | week_monday   | +---------------------+-------------+---------------+ | 2017-11-05T00:00:00 | 45          | 44            | +---------------------+-------------+---------------*/

FORMAT_DATETIME

FORMAT_DATETIME(format_string,datetime_expr)

Description

Formats aDATETIME value according to a specified format string.

Definitions

• format_string: ASTRING value that contains theformat elements to use withdatetime_expr.
• datetime_expr: ADATETIME value that represents the date and time toformat.

Return Data Type

STRING

Examples

SELECTFORMAT_DATETIME("%c",DATETIME"2008-12-25 15:30:00")ASformatted;/*--------------------------+ | formatted                | +--------------------------+ | Thu Dec 25 15:30:00 2008 | +--------------------------*/

SELECTFORMAT_DATETIME("%b-%d-%Y",DATETIME"2008-12-25 15:30:00")ASformatted;/*-------------+ | formatted   | +-------------+ | Dec-25-2008 | +-------------*/

SELECTFORMAT_DATETIME("%b %Y",DATETIME"2008-12-25 15:30:00")ASformatted;/*-------------+ | 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 isn't used, the default value isMONTH.LAST_DAY supports the following values fordate_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Equivalent to 7DAYs.
• WEEK(<WEEKDAY>).<WEEKDAY> represents the starting day of the week.Valid values areSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.
• ISOWEEK. UsesISO 8601 week boundaries. ISO weeks beginon Monday.
• ISOYEAR. Uses theISO 8601 week-numbering year boundary.The ISO year boundary is the Monday of the first week whose Thursday belongsto the corresponding Gregorian calendar year.

Return Data Type

DATE

Example

These both return the last day of the month:

SELECTLAST_DAY(DATETIME'2008-11-25',MONTH)ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-30 | +------------*/

SELECTLAST_DAY(DATETIME'2008-11-25')ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-30 | +------------*/

This returns the last day of the year:

SELECTLAST_DAY(DATETIME'2008-11-25 15:30:00',YEAR)ASlast_day/*------------+ | last_day   | +------------+ | 2008-12-31 | +------------*/

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

SELECTLAST_DAY(DATETIME'2008-11-10 15:30:00',WEEK(SUNDAY))ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-15 | +------------*/

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

SELECTLAST_DAY(DATETIME'2008-11-10 15:30:00',WEEK(MONDAY))ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-16 | +------------*/

PARSE_DATETIME

PARSE_DATETIME(format_string,datetime_string)

Description

Converts aSTRING value to aDATETIME value.

Definitions

• format_string: ASTRING value that contains theformat elements to use withdatetime_string.
• datetime_string: ASTRING value that represents the date and time toparse.

Details

Each element indatetime_string must have a corresponding element informat_string. The location of each element informat_string must match thelocation of each element indatetime_string.

-- This works because elements on both sides match.SELECTPARSE_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.SELECTPARSE_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.SELECTPARSE_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.SELECTPARSE_DATETIME("%c","Thu Dec 25 07:30:00 2008");

The format string fully supports most format elements, except for%P.

The following additional considerations apply when using thePARSE_DATETIMEfunction:

• Unspecified fields. Any unspecified field is initialized from1970-01-01 00:00:00.0. For example, if the year is unspecified then itdefaults to1970.
• Case insensitivity. Names, such asMonday andFebruary,are case insensitive.
• Whitespace. One or more consecutive white spaces in the format stringmatches zero or more consecutive white spaces in theDATETIME string. Leading and trailingwhite spaces in theDATETIME string are alwaysallowed, even if they aren't in the format string.
• Format precedence. When two or more format elements have overlappinginformation, the last one generally overrides any earlier ones, with someexceptions. For example, both%F and%Y affect the year, so the earlierelement overrides the later. See the descriptionsof%s,%C, and%y inSupported Format Elements For DATETIME.
• Format divergence.%p can be used witham,AM,pm, andPM.
• Mixed ISO and non-ISO elements. The ISO format elements are%G,%g,%J, and%V. When these ISO elements are used together with other non-ISOelements, the ISO elements are ignored, resulting in different values. Forexample, the function arguments('%g %J', '8405') return a value with theyear1984, whereas the arguments('%g %j', '8405') return a value withthe year1970 because the ISO element%g is ignored.
• Numeric values after%G input values. Any input string value thatcorresponds to the%G format element requires a whitespace or non-digitcharacter as a separator from numeric values that follow. This is a knownissue in GoogleSQL. For example, the function arguments('%G%V','2020 50') or('%G-%V','2020-50') work, but not('%G%V','202050').For input values before the corresponding%G value, no separator isneeded. For example, the arguments('%V%G','502020') work. The separatorafter the%G values identifies the end of the specified ISO year value sothat the function can parse properly.

Return Data Type

DATETIME

Examples

The following examples parse aSTRING literal as aDATETIME.

SELECTPARSE_DATETIME('%Y-%m-%d %H:%M:%S','1998-10-18 13:45:55')ASdatetime;/*---------------------+ | datetime            | +---------------------+ | 1998-10-18T13:45:55 | +---------------------*/

SELECTPARSE_DATETIME('%m/%d/%Y %I:%M:%S %p','8/30/2018 2:23:38 pm')ASdatetime;/*---------------------+ | datetime            | +---------------------+ | 2018-08-30T14:23:38 | +---------------------*/

The following example parses aSTRING literalcontaining a date in a natural language format as aDATETIME.

SELECTPARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')ASdatetime;/*---------------------+ | datetime            | +---------------------+ | 2018-12-19T00:00:00 | +---------------------*/