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 defined DATE rangeand TIMESTAMP range.
Function list
| Name | Summary |
|---|---|
| CURRENT_TIMESTAMP | Returns the current date and time as a TIMESTAMP object. |
| EXTRACT | Extracts part of a TIMESTAMP value. |
| FORMAT_TIMESTAMP | Formats a TIMESTAMP value according to the specified format string. |
| PARSE_TIMESTAMP | Converts a STRING value to a TIMESTAMP value. |
| STRING | Converts a TIMESTAMP value to a STRING value. |
| TIMESTAMP | Constructs a TIMESTAMP value. |
| TIMESTAMP_ADD | Adds a specified time interval to a TIMESTAMP value. |
| TIMESTAMP_DIFF | Gets the number of unit boundaries between two TIMESTAMP values at a particular time granularity. |
| TIMESTAMP_MICROS | Converts the number of microseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
| TIMESTAMP_MILLIS | Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
| TIMESTAMP_SECONDS | Converts the number of seconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
| TIMESTAMP_SUB | Subtracts a specified time interval from a TIMESTAMP value. |
| TIMESTAMP_TRUNC | Truncates a TIMESTAMP value. |
| UNIX_MICROS | Converts a TIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC. |
| UNIX_MILLIS | Converts a TIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC. |
| UNIX_SECONDS | Converts a TIMESTAMP 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 does not 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 date and time is recorded at the start of the querystatement which contains this function, not when this specific function isevaluated.
Supported Input Types
Not applicable
Result Data Type
TIMESTAMP
Examples
SELECT CURRENT_TIMESTAMP() AS now;/*--------------------------------* | now | +--------------------------------+ | 2020-06-02 23:57:12.120174 UTC | *--------------------------------*/EXTRACT
EXTRACT(part FROM timestamp_expression [AT TIME ZONE time_zone])Description
Returns a value that corresponds to the specified part froma supplied timestamp_expression. This function supports an optionaltime_zone parameter. SeeTime zone definitions for informationon how to specify a time zone.
Allowed part values are:
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYOFWEEK: 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 week 0.WEEK(<WEEKDAY>): Returns the week number oftimestamp_expressionin therange [0, 53]. Weeks begin onWEEKDAY.datetimes prior to the firstWEEKDAYof the year are in week 0. Valid values forWEEKDAYareSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.ISOWEEK: Returns the ISO 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 the ISO 8601week-numbering year, which is the Gregorian calendar year containing theThursday of the week to whichdate_expressionbelongs.DATEDATETIMETIME
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 the DAYtime part.
SELECT EXTRACT( DAY FROM TIMESTAMP('2008-12-25 05:30:00+00') AT TIME ZONE 'UTC') AS the_day_utc, EXTRACT( DAY FROM TIMESTAMP('2008-12-25 05:30:00+00') AT TIME ZONE 'America/Los_Angeles') AS the_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 type TIMESTAMP.
SELECT EXTRACT(ISOYEAR FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS week-- 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 | *---------+---------+------+------*/SELECT TIMESTAMP("2007-12-31 12:00:00+00") AS timestamp_value, EXTRACT(ISOYEAR FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS week-- 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 | *---------+---------+------+------*/SELECT TIMESTAMP("2009-01-01 12:00:00+00") AS timestamp_value, EXTRACT(ISOYEAR FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS week-- 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 | *---------+---------+------+------*/SELECT TIMESTAMP("2009-12-31 12:00:00+00") AS timestamp_value, EXTRACT(ISOYEAR FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS week-- 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 | *---------+---------+------+------*/SELECT TIMESTAMP("2017-01-02 12:00:00+00") AS timestamp_value, EXTRACT(ISOYEAR FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS week-- 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 | *---------+---------+------+------*/SELECT TIMESTAMP("2017-05-26 12:00:00+00") AS timestamp_value, EXTRACT(ISOYEAR FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS isoyear, EXTRACT(ISOWEEK FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS isoweek, EXTRACT(YEAR FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS year, EXTRACT(WEEK FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS week-- 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.
SELECT EXTRACT(WEEK(SUNDAY) FROM TIMESTAMP("2017-11-06 00:00:00+00")) AS week_sunday, EXTRACT(WEEK(MONDAY) FROM TIMESTAMP("2017-11-06 00:00:00+00")) AS week_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[, time_zone])Description
Formats a timestamp according to the specified format_string.
See Format elements for date and time partsfor a list of format elements that this function supports.
Return Data Type
STRING
Example
SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2050-12-25 15:30:55+00", "UTC") AS formatted;/*--------------------------* | formatted | +--------------------------+ | Sun Dec 25 15:30:55 2050 | *--------------------------*/SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2050-12-25 15:30:55+00") AS formatted;/*-------------* | formatted | +-------------+ | Dec-25-2050 | *-------------*/SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2050-12-25 15:30:55+00") AS formatted;/*-------------* | formatted | +-------------+ | Dec 2050 | *-------------*/SELECT FORMAT_TIMESTAMP("%Y-%m-%dT%H:%M:%SZ", TIMESTAMP "2050-12-25 15:30:55", "UTC") AS formatted;/*+---------------------* | formatted | +----------------------+ | 2050-12-25T15:30:55Z | *----------------------*/PARSE_TIMESTAMP
PARSE_TIMESTAMP(format_string, timestamp_string[, time_zone])Description
Converts a string representation of a timestamp to aTIMESTAMP object.
format_string contains the format elementsthat define how timestamp_string is formatted. Each element intimestamp_string must have a corresponding element in format_string. Thelocation of each element in format_string must match the location ofeach element in timestamp_string.
-- This works because elements on both sides match.SELECT PARSE_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.SELECT PARSE_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.SELECT PARSE_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.SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");The format string fully supports most format elements, except for%P.
When using PARSE_TIMESTAMP, keep the following in mind:
- 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, UTC. 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 are not 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
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;-- 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
SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;/*-------------------------------* | 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_expressionmust include atimestamp literal.Ifstring_expressionincludes a time zone in the timestamp literal, donot 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 to specify a time zone. Ifno time zone is specified, the default time zone, UTC,is used.
Return Data Type
TIMESTAMP
Examples
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_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 | *-------------------------*/SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_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 | *-------------------------*/SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_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 | *-------------------------*/SELECT TIMESTAMP(DATETIME "2008-12-25 15:30:00") AS timestamp_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 | *-------------------------*/SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_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, INTERVAL int64_expression date_part)Description
Adds int64_expression units of date_part to the timestamp, independent ofany time zone.
TIMESTAMP_ADD supports the following values for date_part:
MICROSECONDMILLISECONDSECONDMINUTEHOUR. Equivalent to 60MINUTEparts.DAY. Equivalent to 24HOURparts.
Return Data Types
TIMESTAMP
Example
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS original, TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;-- 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 two TIMESTAMP 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. Ifyou passed inTIMESTAMPvalues for the first arguments,granularitycanbe:MICROSECONDMILLISECONDSECONDMINUTEHOUR. Equivalent to 60MINUTEs.DAY. Equivalent to 24HOURs.
Details
If end_timestamp is earlier than start_timestamp, the output is negative.Produces an error if the computation overflows, such as if the differencein microsecondsbetween the two TIMESTAMP values overflows.
Return Data Type
INT64
Example
SELECT TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp, TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp, TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;-- 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 | *-------------------------+-------------------------+-------*/In the following example, the first timestamp occurs before thesecond timestamp, resulting in a negative output.
SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY) AS negative_diff;/*---------------* | negative_diff | +---------------+ | -61 | *---------------*/In this example, the result is 0 because only the number of whole specifiedHOUR intervals are included.
SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR) AS diff;/*---------------* | diff | +---------------+ | 0 | *---------------*/TIMESTAMP_MICROS
TIMESTAMP_MICROS(int64_expression)Description
Interprets int64_expression as the number of microseconds since 1970-01-0100:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_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
TIMESTAMP_MILLIS(int64_expression)Description
Interprets int64_expression as the number of milliseconds since 1970-01-0100:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_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
TIMESTAMP_SECONDS(int64_expression)Description
Interprets int64_expression as the number of seconds since 1970-01-01 00:00:00UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_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_SUB(timestamp_expression, INTERVAL int64_expression date_part)Description
Subtracts int64_expression units of date_part from the timestamp,independent of any time zone.
TIMESTAMP_SUB supports the following values for date_part:
MICROSECONDMILLISECONDSECONDMINUTEHOUR. Equivalent to 60MINUTEparts.DAY. Equivalent to 24HOURparts.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS original, TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;-- 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_TRUNC(timestamp_expression, granularity[, time_zone])Description
Truncates a TIMESTAMP value at a particular time granularity. The TIMESTAMPvalue is always rounded to the beginning of granularity.
Definitions
timestamp_expression: TheTIMESTAMPvalue to truncate.granularity: The datetime part that represents the granularity. Ifyou passed in aTIMESTAMPvalue for the first argument,granularitycanbe: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.DAY: The day in the Gregorian calendar year that contains theTIMESTAMPvalue.WEEK: The first day in the week that contains theTIMESTAMPvalue. Weeks begin on Sundays.WEEKis equivalent toWEEK(SUNDAY).WEEK(WEEKDAY): The first day in the week that contains theTIMESTAMPvalue. Weeks begin onWEEKDAY.WEEKDAYmust be one of thefollowing:SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,orSATURDAY.ISOWEEK: The first day in the ISO 8601 week that containstheTIMESTAMPvalue. 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 theTIMESTAMPvalue.QUARTER: The first day in the quarter that contains theTIMESTAMPvalue.YEAR: The first day in the year that contains theTIMESTAMPvalue.ISOYEAR: The first day in the ISO 8601 week-numbering yearthat contains theTIMESTAMPvalue. The ISO year is theMonday of the first week where Thursday belongs to the correspondingGregorian calendar year.
time_zone: Use this parameter if you want to use a time zone other thanthe default time zone, UTC, as part of thetruncate operation. This can be:MINUTEHOURDAYWEEKWEEK(<WEEKDAY>)ISOWEEKMONTHQUARTERYEARISOYEAR
When truncating a timestamp to MINUTEorHOUR parts, TIMESTAMP_TRUNC determines the civil time of thetimestamp in the specified (or default) time zoneand subtracts the minutes and seconds (when truncating to HOUR) or the seconds(when truncating to MINUTE) from that timestamp.While this provides intuitive results in most cases, the result isnon-intuitive near daylight savings transitions that are not hour-aligned.
Return Data Type
TIMESTAMP
Examples
SELECT TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc, TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;-- 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 the timestamp_expression in UTC time. The secondcolumn shows the output of TIMESTAMP_TRUNC using weeks that start on Monday.Because the timestamp_expression falls on a Sunday in UTC, TIMESTAMP_TRUNCtruncates it to the preceding Monday. The third column shows the same functionwith the optional Time zone definitionargument 'Pacific/Auckland'. Here, the function truncates thetimestamp_expression using New Zealand Daylight Time, where it falls on aMonday.
SELECT timestamp_value AS timestamp_value, TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "UTC") AS utc_truncated, TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "Pacific/Auckland") AS nzdt_truncatedFROM (SELECT TIMESTAMP("2017-11-06 00:00:00+12") AS timestamp_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 original timestamp_expression is in theGregorian calendar year 2015. However, TIMESTAMP_TRUNC with the ISOYEAR datepart truncates the timestamp_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 the timestamp_expression2015-06-15 00:00:00+00 is 2014-12-29.
SELECT TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary, EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_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 since 1970-01-01 00:00:00 UTC.
Return Data Type
INT64
Examples
SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") AS micros;/*------------------* | micros | +------------------+ | 1230219000000000 | *------------------*/UNIX_MILLIS
UNIX_MILLIS(timestamp_expression)Description
Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncateshigher levels of precision by rounding down to the beginning of the millisecond.
Return Data Type
INT64
Examples
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00+00") AS millis;/*---------------* | millis | +---------------+ | 1230219000000 | *---------------*/SELECT UNIX_MILLIS(TIMESTAMP "1970-01-01 00:00:00.0018+00") AS millis;/*---------------* | millis | +---------------+ | 1 | *---------------*/UNIX_SECONDS
UNIX_SECONDS(timestamp_expression)Description
Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higherlevels of precision by rounding down to the beginning of the second.
Return Data Type
INT64
Examples
SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS seconds;/*------------* | seconds | +------------+ | 1230219000 | *------------*/SELECT UNIX_SECONDS(TIMESTAMP "1970-01-01 00:00:01.8+00") AS seconds;/*------------* | seconds | +------------+ | 1 | *------------*/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 is usually converted toa human-readable format consisting of a civil date and time(YYYY-MM-DD HH:MM:SS)and a time zone. This is not the internal representation of theTIMESTAMP; it is 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 like PARSE_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 like EXTRACT 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 the TIMESTAMP type, seeTime zones.
