Timestamp functions in GoogleSQL
GoogleSQL for Spanner 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
| Name | Summary |
|---|---|
CURRENT_TIMESTAMP | Returns the current date and time as aTIMESTAMP object. |
EXTRACT | Extracts part of aTIMESTAMP value. |
FORMAT_TIMESTAMP | Formats aTIMESTAMP value according to the specified format string. |
PARSE_TIMESTAMP | Converts aSTRING value to aTIMESTAMP value. |
PENDING_COMMIT_TIMESTAMP | Write a pending commit timestamp. |
STRING (Timestamp) | Converts aTIMESTAMP value to aSTRING value. |
TIMESTAMP | Constructs aTIMESTAMP value. |
TIMESTAMP_ADD | Adds a specified time interval to aTIMESTAMP value. |
TIMESTAMP_DIFF | Gets the number of unit boundaries between twoTIMESTAMP values at a particular time granularity. |
TIMESTAMP_MICROS | Converts the number of microseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP. |
TIMESTAMP_MILLIS | Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP. |
TIMESTAMP_SECONDS | Converts the number of seconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP. |
TIMESTAMP_SUB | Subtracts a specified time interval from aTIMESTAMP value. |
TIMESTAMP_TRUNC | Truncates aTIMESTAMP value at a particular granularity. |
UNIX_MICROS | Converts aTIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC. |
UNIX_MILLIS | Converts aTIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC. |
UNIX_SECONDS | Converts aTIMESTAMP value to the number of seconds since 1970-01-01 00:00:00 UTC. |
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()CURRENT_TIMESTAMPDescription
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-02T23:58:40.347847393Z | +--------------------------------*/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:
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOURDAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day ofof the week.DAYDAYOFYEARWEEK: 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.ISOWEEK: Returns theISO 8601 weeknumber of thedatetime_expression.ISOWEEKs begin on Monday. Return valuesare in the range [1, 53]. The firstISOWEEKof each ISO year begins on theMonday before the first Thursday of the Gregorian calendar year.MONTHQUARTERYEARISOYEAR: Returns theISO 8601week-numbering year, which is the Gregorian calendar year containing theThursday of the week to whichdate_expressionbelongs.DATE
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:
- If
partisDATE, the function returns aDATEobject.
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 | +---------+---------+------+------*/FORMAT_TIMESTAMP
FORMAT_TIMESTAMP(format_string,timestamp_expr[,time_zone])Description
Formats aTIMESTAMP value according to the specified format string.
Definitions
format_string: ASTRINGvalue that contains theformat elements to use withtimestamp_expr.timestamp_expr: ATIMESTAMPvalue that represents the timestamp to format.time_zone: ASTRINGvalue 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: ASTRINGvalue that contains theformat elements to use withtimestamp_string.timestamp_string: ASTRINGvalue that represents the timestamp to parse.time_zone: ASTRINGvalue 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%g,%G,%j,%P,%u,%U,%V,%w, and%W.
The following additional considerations apply when using thePARSE_TIMESTAMPfunction:
- Unspecified fields. Any unspecified field is initialized from
1970-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, America/Los_Angeles. For instance, if the yearis unspecified then it defaults to1970, and so on. - Case insensitivity. Names, such as
Monday,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
%Fand%Yaffect the year), the last onegenerally overrides any earlier ones, with some exceptions (see thedescriptions of%s,%C, and%y). - Format divergence.
%pcan be used witham,AM,pm, andPM.
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-25T15:30:00Z | +------------------------*/PENDING_COMMIT_TIMESTAMP
PENDING_COMMIT_TIMESTAMP()Description
Use thePENDING_COMMIT_TIMESTAMP() function in a DMLINSERT orUPDATEstatement to write the pending commit timestamp, that is, the commit timestampof the write when it commits, into a column of typeTIMESTAMP.
Spanner selects the commit timestamp when the transaction commits. ThePENDING_COMMIT_TIMESTAMP function may only be used as a value for INSERT orUPDATE of an appropriately typed column. It can't be used in SELECT, or as theinput to any other scalar expression.
PENDING_COMMIT_TIMESTAMP function, the table andany derived index is unreadable to any future SQL statements in the transaction.Because of this, the change stream can't extract the previous value for thecolumn that has a pending commit timestamp, if the column is modified againlater in the same transaction. You must write commit timestamps as the laststatement in a transaction to prevent the possibility of trying to read thetable. If you try to read the table, then GoogleSQL produces an error.Return Data Type
TIMESTAMP
Example
The following DML statement updates theLastUpdated column in the Singerstable with the commit timestamp.
UPDATEPerformancesSETLastUpdated=PENDING_COMMIT_TIMESTAMP()WHERESingerId=1ANDVenueId=2ANDEventDate="2015-10-21"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])Description
string_expression[, time_zone]: Converts a string to atimestamp.string_expressionmust include atimestamp literal.Ifstring_expressionincludes 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.
This function supports an optionalparameter tospecify a time zone. Ifno time zone is specified, the default time zone, America/Los_Angeles,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-25T15:30:00Z | +----------------------*/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-25T23:30:00Z | +----------------------*/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-25T15:30:00Z | +----------------------*/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-25T08:00:00Z | +----------------------*/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:
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR. Equivalent to 60MINUTEparts.DAY. Equivalent to 24HOURparts.
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-25T15:30:00Z | 2008-12-25T15:40:00Z | +------------------------+------------------------*/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 startingTIMESTAMPvalue.end_timestamp: The endingTIMESTAMPvalue.granularity: The timestamp part that represents the granularity. This can be:NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR. 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 nanosecondsbetween the twoTIMESTAMP values overflows.
Return Data Type
INT64
Example
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-07T10:20:00Z | 2008-12-25T15:30:00Z | 13410 | +------------------------+------------------------+-------*/In the following example, the first timestamp occurs before thesecond timestamp, resulting in a negative output.
SELECTTIMESTAMP_DIFF(TIMESTAMP"2018-08-14",TIMESTAMP"2018-10-14",DAY)ASnegative_diff;/*---------------+ | negative_diff | +---------------+ | -61 | +---------------*/In this example, the result is 0 because only the number of whole specifiedHOUR intervals are included.
SELECTTIMESTAMP_DIFF("2001-02-01 01:00:00","2001-02-01 00:00:01",HOUR)ASdiff;/*---------------+ | diff | +---------------+ | 0 | +---------------*/TIMESTAMP_MICROS
TIMESTAMP_MICROS(int64_expression)Description
Interpretsint64_expression as the number of microseconds since 1970-01-0100:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
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-25T15:30:00Z | +------------------------*/TIMESTAMP_MILLIS
TIMESTAMP_MILLIS(int64_expression)Description
Interpretsint64_expression as the number of milliseconds since 1970-01-0100:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
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-25T15:30:00Z | +------------------------*/TIMESTAMP_SECONDS
TIMESTAMP_SECONDS(int64_expression)Description
Interpretsint64_expression as the number of seconds since 1970-01-01 00:00:00UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
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-25T15:30:00Z | +------------------------*/TIMESTAMP_SUB
TIMESTAMP_SUB(timestamp_expression,INTERVALint64_expressiondate_part)Description
Subtractsint64_expression units ofdate_part from the timestamp,independent of any time zone.
TIMESTAMP_SUB supports the following values fordate_part:
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR. Equivalent to 60MINUTEparts.DAY. Equivalent to 24HOURparts.
Return Data Type
TIMESTAMP
Example
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-25T15:30:00Z | 2008-12-25T15:20:00Z | +------------------------+------------------------*/TIMESTAMP_TRUNC
TIMESTAMP_TRUNC(timestamp_value,timestamp_granularity[,time_zone])Description
Truncates aTIMESTAMP value at a particular granularity.
Definitions
timestamp_value: ATIMESTAMPvalue to truncate.timestamp_granularity: The truncation granularity for aTIMESTAMPvalue.Date granularities andtime granularities can be used.
Note: When truncating a timestamp totime_zone: A time zone to use with theTIMESTAMPvalue.Time zone parts can be used.Use this argument if you want to use a time zone other thanthe default time zone, America/Los_Angeles, as part of thetruncate operation.MINUTEorHOURparts, 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.WEEKis equivalent toWEEK(SUNDAY).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
NANOSECOND: If used, nothing is truncated from the value.MICROSECOND: The nearest lesser than or equal microsecond.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
MINUTEHOURDAYWEEKISOWEEKMONTHQUARTERYEARISOYEAR
Details
The resulting value is always rounded to the beginning ofgranularity.
Return Data Type
TIMESTAMP
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-25T00:00:00Z | 2008-12-25T08:00:00Z | +------------------------+------------------------*/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./*------------------------+----------------+ | parsed | isoyear_number | +------------------------+----------------+ | 2014-12-29T08:00:00Z | 2015 | +------------------------+----------------*/UNIX_MICROS
UNIX_MICROS(timestamp_expression)Description
Returns the number of microseconds since1970-01-01 00:00:00 UTC.Truncates higher levels of precision byrounding down to the beginning of the microsecond.
Return Data Type
INT64
Examples
SELECTUNIX_MICROS(TIMESTAMP"2008-12-25 15:30:00+00")ASmicros;/*------------------+ | micros | +------------------+ | 1230219000000000 | +------------------*/SELECTUNIX_MICROS(TIMESTAMP"1970-01-01 00:00:00.0000018+00")ASmicros;/*------------------+ | micros | +------------------+ | 1 | +------------------*/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, America/Los_Angeles,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.
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.