GoogleSQL for BigQuery supports the following timestamp functions.

IMPORTANT: Before working with these functions, you need to understandthe difference between the formats in which timestamps are stored and displayed,and how time zones are used for the conversion between these formats.To learn more, seeHow time zones work with timestamp functions.

NOTE: These functions return a runtime error if overflow occurs; resultvalues are bounded by the definedDATE rangeandTIMESTAMP range.

Function list

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP

Description

Returns the current date and time as a timestamp object. The timestamp iscontinuous, non-ambiguous, has exactly 60 seconds per minute and doesn't repeatvalues over the leap second. Parentheses are optional.

This function handles leap seconds by smearing them across a window of 20 hoursaround the inserted leap second.

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

Supported Input Types

Not applicable

Result Data Type

TIMESTAMP

Examples

SELECTCURRENT_TIMESTAMP()ASnow;/*--------------------------------+ | now                            | +--------------------------------+ | 2020-06-02 23:57:12.120174 UTC | +--------------------------------*/

EXTRACT

EXTRACT(partFROMtimestamp_expression[ATTIMEZONEtime_zone])

Description

Returns a value that corresponds to the specifiedpart froma suppliedtimestamp_expression. This function supports an optionaltime_zone parameter. SeeTime zone definitions for informationon how to specify a time zone.

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 oftimestamp_expression in therange [0, 53]. Weeks begin onWEEKDAY.datetimes prior to the firstWEEKDAY of the year are in week 0. Valid values 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
• DATETIME
• 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, the function returns aDATE object.

Examples

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

SELECTEXTRACT(DAYFROMTIMESTAMP('2008-12-25 05:30:00+00')ATTIMEZONE'UTC')ASthe_day_utc,EXTRACT(DAYFROMTIMESTAMP('2008-12-25 05:30:00+00')ATTIMEZONE'America/Los_Angeles')ASthe_day_california/*-------------+--------------------+ | the_day_utc | the_day_california | +-------------+--------------------+ | 25          | 24                 | +-------------+--------------------*/

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

SELECTEXTRACT(ISOYEARFROMTIMESTAMP("2005-01-03 12:34:56+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2005-01-03 12:34:56+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2005-01-03 12:34:56+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2005-01-03 12:34:56+00"))ASweek-- Display of results may differ, depending upon the environment and-- time zone where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2005    | 1       | 2005 | 1    | +---------+---------+------+------*/

SELECTTIMESTAMP("2007-12-31 12:00:00+00")AStimestamp_value,EXTRACT(ISOYEARFROMTIMESTAMP("2007-12-31 12:00:00+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2007-12-31 12:00:00+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2007-12-31 12:00:00+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2007-12-31 12:00:00+00"))ASweek-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2008    | 1       | 2007 | 52    | +---------+---------+------+------*/

SELECTTIMESTAMP("2009-01-01 12:00:00+00")AStimestamp_value,EXTRACT(ISOYEARFROMTIMESTAMP("2009-01-01 12:00:00+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2009-01-01 12:00:00+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2009-01-01 12:00:00+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2009-01-01 12:00:00+00"))ASweek-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2009    | 1       | 2009 | 0    | +---------+---------+------+------*/

SELECTTIMESTAMP("2009-12-31 12:00:00+00")AStimestamp_value,EXTRACT(ISOYEARFROMTIMESTAMP("2009-12-31 12:00:00+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2009-12-31 12:00:00+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2009-12-31 12:00:00+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2009-12-31 12:00:00+00"))ASweek-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2009    | 53      | 2009 | 52   | +---------+---------+------+------*/

SELECTTIMESTAMP("2017-01-02 12:00:00+00")AStimestamp_value,EXTRACT(ISOYEARFROMTIMESTAMP("2017-01-02 12:00:00+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2017-01-02 12:00:00+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2017-01-02 12:00:00+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2017-01-02 12:00:00+00"))ASweek-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2017    | 1       | 2017 | 1    | +---------+---------+------+------*/

SELECTTIMESTAMP("2017-05-26 12:00:00+00")AStimestamp_value,EXTRACT(ISOYEARFROMTIMESTAMP("2017-05-26 12:00:00+00"))ASisoyear,EXTRACT(ISOWEEK FROMTIMESTAMP("2017-05-26 12:00:00+00"))ASisoweek,EXTRACT(YEARFROMTIMESTAMP("2017-05-26 12:00:00+00"))ASyear,EXTRACT(WEEKFROMTIMESTAMP("2017-05-26 12:00:00+00"))ASweek-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*---------+---------+------+------+ | isoyear | isoweek | year | week | +---------+---------+------+------+ | 2017    | 21      | 2017 | 21   | +---------+---------+------+------*/

In the following example,timestamp_expression falls on a Monday.EXTRACTcalculates the first column using weeks that begin on Sunday, and it calculatesthe second column using weeks that begin on Monday.

SELECTEXTRACT(WEEK(SUNDAY)FROMTIMESTAMP("2017-11-06 00:00:00+00"))ASweek_sunday,EXTRACT(WEEK(MONDAY)FROMTIMESTAMP("2017-11-06 00:00:00+00"))ASweek_monday-- Display of results may differ, depending upon the environment and time zone-- where this query was executed./*-------------+---------------+ | week_sunday | week_monday   | +-------------+---------------+ | 45          | 44            | +-------------+---------------*/

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string,timestamp_expr[,time_zone])

Description

Formats aTIMESTAMP value according to the specified format string.

Definitions

• format_string: ASTRING value that contains theformat elements to use withtimestamp_expr.
• timestamp_expr: ATIMESTAMP value that represents the timestamp to format.
• time_zone: ASTRING value that represents a time zone. For moreinformation about how to use a time zone with a timestamp, seeTime zone definitions.

Return Data Type

STRING

Examples

SELECTFORMAT_TIMESTAMP("%c",TIMESTAMP"2050-12-25 15:30:55+00","UTC")ASformatted;/*--------------------------+ | formatted                | +--------------------------+ | Sun Dec 25 15:30:55 2050 | +--------------------------*/

SELECTFORMAT_TIMESTAMP("%b-%d-%Y",TIMESTAMP"2050-12-25 15:30:55+00")ASformatted;/*-------------+ | formatted   | +-------------+ | Dec-25-2050 | +-------------*/

SELECTFORMAT_TIMESTAMP("%b %Y",TIMESTAMP"2050-12-25 15:30:55+00")ASformatted;/*-------------+ | formatted   | +-------------+ | Dec 2050    | +-------------*/

SELECTFORMAT_TIMESTAMP("%Y-%m-%dT%H:%M:%S%Z",TIMESTAMP"2050-12-25 15:30:55","UTC")ASformatted;/*+-----------------------+ |       formatted        | +------------------------+ | 2050-12-25T15:30:55UTC | +------------------------*/

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string,timestamp_string[,time_zone])

Description

Converts aSTRING value to aTIMESTAMP value.

Definitions

• format_string: ASTRING value that contains theformat elements to use withtimestamp_string.
• timestamp_string: ASTRING value that represents the timestamp to parse.
• time_zone: ASTRING value that represents a time zone. For moreinformation about how to use a time zone with a timestamp, seeTime zone definitions.

Details

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

-- This works because elements on both sides match.SELECTPARSE_TIMESTAMP("%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_TIMESTAMP("%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_TIMESTAMP("%a %b %e %I:%M:%S","Thu Dec 25 07:30:00 2008");-- This works because %c can find all matching elements in timestamp_string.SELECTPARSE_TIMESTAMP("%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_TIMESTAMPfunction:

• Unspecified fields. Any unspecified field is initialized from1970-01-0100:00:00.0. This initialization value uses the time zone specified by thefunction's time zone argument, if present. If not, the initialization valueuses the default time zone, UTC. For instance, if the yearis unspecified then it defaults to1970, and so on.
• Case insensitivity. Names, such asMonday,February, and so on, arecase insensitive.
• Whitespace. One or more consecutive white spaces in the format stringmatches zero or more consecutive white spaces in the timestamp string. Inaddition, leading and trailing white spaces in the timestamp string are alwaysallowed, even if they aren't in the format string.
• Format precedence. When two (or more) format elements have overlappinginformation (for example both%F and%Y affect the year), the last onegenerally overrides any earlier ones, with some exceptions (see thedescriptions of%s,%C, and%y).
• 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

TIMESTAMP

Example

SELECTPARSE_TIMESTAMP("%c","Thu Dec 25 07:30:00 2008")ASparsed;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | parsed                  | +-------------------------+ | 2008-12-25 07:30:00 UTC | +-------------------------*/

STRING

STRING(timestamp_expression[,time_zone])

Description

Converts a timestamp to a string. Supports an optionalparameter to specify a time zone. SeeTime zone definitions for informationon how to specify a time zone.

Return Data Type

STRING

Example

SELECTSTRING(TIMESTAMP"2008-12-25 15:30:00+00","UTC")ASstring;/*-------------------------------+ | string                        | +-------------------------------+ | 2008-12-25 15:30:00+00        | +-------------------------------*/

TIMESTAMP

TIMESTAMP(string_expression[,time_zone])TIMESTAMP(date_expression[,time_zone])TIMESTAMP(datetime_expression[,time_zone])

Description

• string_expression[, time_zone]: Converts a string to atimestamp.string_expression must include atimestamp literal.Ifstring_expression includes a time zone in the timestamp literal,don't include an explicittime_zoneargument.
• date_expression[, time_zone]: Converts a date to a timestamp.The value returned is the earliest timestamp that falls withinthe given date.
• datetime_expression[, time_zone]: Converts adatetime to a timestamp.

This function supports an optionalparameter tospecify a time zone. Ifno time zone is specified, the default time zone, UTC,is used.

Return Data Type

TIMESTAMP

Examples

SELECTTIMESTAMP("2008-12-25 15:30:00+00")AStimestamp_str;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_str           | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

SELECTTIMESTAMP("2008-12-25 15:30:00","America/Los_Angeles")AStimestamp_str;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_str           | +-------------------------+ | 2008-12-25 23:30:00 UTC | +-------------------------*/

SELECTTIMESTAMP("2008-12-25 15:30:00 UTC")AStimestamp_str;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_str           | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

SELECTTIMESTAMP(DATETIME"2008-12-25 15:30:00")AStimestamp_datetime;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_datetime      | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

SELECTTIMESTAMP(DATE"2008-12-25")AStimestamp_date;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_date          | +-------------------------+ | 2008-12-25 00:00:00 UTC | +-------------------------*/

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression,INTERVALint64_expressiondate_part)

Description

Addsint64_expression units ofdate_part to the timestamp, independent ofany time zone.

TIMESTAMP_ADD supports the following values fordate_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR. Equivalent to 60MINUTE parts.
• DAY. Equivalent to 24HOUR parts.

Return Data Types

TIMESTAMP

Example

SELECTTIMESTAMP("2008-12-25 15:30:00+00")ASoriginal,TIMESTAMP_ADD(TIMESTAMP"2008-12-25 15:30:00+00",INTERVAL10MINUTE)ASlater;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+-------------------------+ | original                | later                   | +-------------------------+-------------------------+ | 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC | +-------------------------+-------------------------*/

TIMESTAMP_DIFF

TIMESTAMP_DIFF(end_timestamp,start_timestamp,granularity)

Description

Gets the number of unit boundaries between twoTIMESTAMP values(end_timestamp -start_timestamp) at a particular time granularity.

Definitions

• start_timestamp: The startingTIMESTAMP value.
• end_timestamp: The endingTIMESTAMP value.

• granularity: The timestamp part that represents the granularity. Ifyou passed inTIMESTAMP values for the first arguments,granularity canbe:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR. Equivalent to 60MINUTEs.
  • DAY. Equivalent to 24HOURs.

Details

Ifend_timestamp is earlier thanstart_timestamp, the output is negative.Produces an error if the computation overflows, such as if the differencein microsecondsbetween the twoTIMESTAMP values overflows.

SELECTTIMESTAMP("2010-07-07 10:20:00+00")ASlater_timestamp,TIMESTAMP("2008-12-25 15:30:00+00")ASearlier_timestamp,TIMESTAMP_DIFF(TIMESTAMP"2010-07-07 10:20:00+00",TIMESTAMP"2008-12-25 15:30:00+00",HOUR)AShours;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+-------------------------+-------+ | later_timestamp         | earlier_timestamp       | hours | +-------------------------+-------------------------+-------+ | 2010-07-07 10:20:00 UTC | 2008-12-25 15:30:00 UTC | 13410 | +-------------------------+-------------------------+-------*/

SELECTTIMESTAMP_DIFF(TIMESTAMP"2018-08-14",TIMESTAMP"2018-10-14",DAY)ASnegative_diff;/*---------------+ | negative_diff | +---------------+ | -61           | +---------------*/

SELECTTIMESTAMP_DIFF("2001-02-01 01:00:00","2001-02-01 00:00:01",HOUR)ASdiff;/*---------------+ | diff          | +---------------+ | 0             | +---------------*/

TIMESTAMP_MICROS(int64_expression)

SELECTTIMESTAMP_MICROS(1230219000000000)AStimestamp_value;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_value         | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

TIMESTAMP_MILLIS(int64_expression)

SELECTTIMESTAMP_MILLIS(1230219000000)AStimestamp_value;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_value         | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

TIMESTAMP_SECONDS(int64_expression)

SELECTTIMESTAMP_SECONDS(1230219000)AStimestamp_value;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+ | timestamp_value         | +-------------------------+ | 2008-12-25 15:30:00 UTC | +-------------------------*/

TIMESTAMP_SUB(timestamp_expression,INTERVALint64_expressiondate_part)

SELECTTIMESTAMP("2008-12-25 15:30:00+00")ASoriginal,TIMESTAMP_SUB(TIMESTAMP"2008-12-25 15:30:00+00",INTERVAL10MINUTE)ASearlier;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+-------------------------+ | original                | earlier                 | +-------------------------+-------------------------+ | 2008-12-25 15:30:00 UTC | 2008-12-25 15:20:00 UTC | +-------------------------+-------------------------*/

TIMESTAMP_TRUNC(timestamp_value,timestamp_granularity[,time_zone])

TIMESTAMP_TRUNC(datetime_value,datetime_granularity)

• 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.

• datetime_value: ADATETIME value to truncate.

• datetime_granularity: The truncation granularity for aDATETIME value.Date granularities andtime granularities can be used.

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

SELECTTIMESTAMP_TRUNC(TIMESTAMP"2008-12-25 15:30:00+00",DAY,"UTC")ASutc,TIMESTAMP_TRUNC(TIMESTAMP"2008-12-25 15:30:00+00",DAY,"America/Los_Angeles")ASla;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+-------------------------+ | utc                     | la                      | +-------------------------+-------------------------+ | 2008-12-25 00:00:00 UTC | 2008-12-25 08:00:00 UTC | +-------------------------+-------------------------*/

In the following example,timestamp_expression has a time zone offset of +12.The first column shows thetimestamp_expression in UTC time. The secondcolumn shows the output ofTIMESTAMP_TRUNC using weeks that start on Monday.Because thetimestamp_expression falls on a Sunday in UTC,TIMESTAMP_TRUNCtruncates it to the preceding Monday. The third column shows the same functionwith the optionalTime zone definitionargument 'Pacific/Auckland'. Here, the function truncates thetimestamp_expression using New Zealand Daylight Time, where it falls on aMonday.

SELECTtimestamp_valueAStimestamp_value,TIMESTAMP_TRUNC(timestamp_value,WEEK(MONDAY),"UTC")ASutc_truncated,TIMESTAMP_TRUNC(timestamp_value,WEEK(MONDAY),"Pacific/Auckland")ASnzdt_truncatedFROM(SELECTTIMESTAMP("2017-11-06 00:00:00+12")AStimestamp_value);-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+-------------------------+-------------------------+ | timestamp_value         | utc_truncated           | nzdt_truncated          | +-------------------------+-------------------------+-------------------------+ | 2017-11-05 12:00:00 UTC | 2017-10-30 00:00:00 UTC | 2017-11-05 11:00:00 UTC | +-------------------------+-------------------------+-------------------------*/

In the following example, the originaltimestamp_expression is in theGregorian calendar year 2015. However,TIMESTAMP_TRUNC with theISOYEAR datepart truncates thetimestamp_expression to the beginning of the ISO year, notthe Gregorian 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 thetimestamp_expression2015-06-15 00:00:00+00 is 2014-12-29.

SELECTTIMESTAMP_TRUNC("2015-06-15 00:00:00+00",ISOYEAR)ASisoyear_boundary,EXTRACT(ISOYEARFROMTIMESTAMP"2015-06-15 00:00:00+00")ASisoyear_number;-- Display of results may differ, depending upon the environment and time zone where this query was executed./*-------------------------+----------------+ | isoyear_boundary        | isoyear_number | +-------------------------+----------------+ | 2014-12-29 00:00:00 UTC | 2015           | +-------------------------+----------------*/

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Description

Returns the number of microseconds since1970-01-01 00:00:00 UTC.

Return Data Type

INT64

Examples

SELECTUNIX_MICROS(TIMESTAMP"2008-12-25 15:30:00+00")ASmicros;/*------------------+ | micros           | +------------------+ | 1230219000000000 | +------------------*/

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

Description

Returns the number of milliseconds since1970-01-01 00:00:00 UTC. Truncateshigher levels of precision by rounding down to the beginning of the millisecond.

Return Data Type

INT64

Examples

SELECTUNIX_MILLIS(TIMESTAMP"2008-12-25 15:30:00+00")ASmillis;/*---------------+ | millis        | +---------------+ | 1230219000000 | +---------------*/

SELECTUNIX_MILLIS(TIMESTAMP"1970-01-01 00:00:00.0018+00")ASmillis;/*---------------+ | millis        | +---------------+ | 1             | +---------------*/

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

Description

Returns the number of seconds since1970-01-01 00:00:00 UTC. Truncates higherlevels of precision by rounding down to the beginning of the second.

Return Data Type

INT64

Examples

SELECTUNIX_SECONDS(TIMESTAMP"2008-12-25 15:30:00+00")ASseconds;/*------------+ | seconds    | +------------+ | 1230219000 | +------------*/

SELECTUNIX_SECONDS(TIMESTAMP"1970-01-01 00:00:01.8+00")ASseconds;/*------------+ | seconds    | +------------+ | 1          | +------------*/

Supplemental materials

How time zones work with timestamp functions

A timestamp represents an absolute point in time, independent of any timezone. However, when a timestamp value is displayed, it's usually converted toa human-readable format consisting of a civil date and time(YYYY-MM-DD HH:MM:SS)and a time zone. This isn't the internal representation of theTIMESTAMP; it's only a human-understandable way to describe the point in timethat the timestamp represents.

Some timestamp functions have a time zone argument. A time zone is needed toconvert between civil time (YYYY-MM-DD HH:MM:SS) and the absolute timerepresented by a timestamp.A function likePARSE_TIMESTAMP takes an input string that represents acivil time and returns a timestamp that represents an absolute time. Atime zone is needed for this conversion. A function likeEXTRACT takes aninput timestamp (absolute time) and converts it to civil time in order toextract a part of that civil time. This conversion requires a time zone.If no time zone is specified, the default time zone, UTC,is used.

Certain date and timestamp functions allow you to override the default time zoneand specify a different one. You can specify a time zone by either supplyingthe time zone name (for example,America/Los_Angeles)or time zone offset from UTC (for example, -08).

To learn more about how time zones work with theTIMESTAMP type, seeTime zones.