Timestamp functions

GoogleSQL for BigQuery supports the following timestamp functions.

IMPORTANT: Before working with these functions, you need to understand the 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, see How time zones work with timestamp functions.

NOTE: These functions return a runtime error if overflow occurs; result values are bounded by the defined DATE range and 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 intervals between two TIMESTAMP values.
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_TIMESTAMP

Description

Returns the current date and time as a timestamp object. The timestamp is continuous, non-ambiguous, has exactly 60 seconds per minute and does not repeat values over the leap second. Parentheses are optional.

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

The current date and time is recorded at the start of the query statement which contains this function, not when this specific function is evaluated.

Supported Input Types

Not applicable

Result Data Type

TIMESTAMP

Examples

SELECT CURRENT_TIMESTAMP() AS now;

/*--------------------------------*
 | now                            |
 +--------------------------------+
 | 2020-06-02 23:57:12.120174 UTC |
 *--------------------------------*/

When a column named current_timestamp is present, the column name and the function call without parentheses are ambiguous. To ensure the function call, add parentheses; to ensure the column name, qualify it with its range variable. For example, the following query selects the function in the now column and the table column in the current_timestamp column.

WITH t AS (SELECT 'column value' AS `current_timestamp`)
SELECT current_timestamp() AS now, t.current_timestamp FROM t;

/*--------------------------------+-------------------*
 | now                            | current_timestamp |
 +--------------------------------+-------------------+
 | 2020-06-02 23:57:12.120174 UTC | column value      |
 *--------------------------------+-------------------*/

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE time_zone])

Description

Returns a value that corresponds to the specified part from a supplied timestamp_expression. This function supports an optional time_zone parameter. See Time zone definitions for information on how to specify a time zone.

Allowed part values are:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day of of the week.
  • DAY
  • DAYOFYEAR
  • WEEK: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.
  • WEEK(<WEEKDAY>): Returns the week number of timestamp_expression in the range [0, 53]. Weeks begin on WEEKDAY. datetimes prior to the first WEEKDAY of the year are in week 0. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  • ISOWEEK: Returns the ISO 8601 week number of the datetime_expression. ISOWEEKs begin on Monday. Return values are in the range [1, 53]. The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Returns the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date_expression belongs.
  • DATE
  • DATETIME
  • TIME

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

Return Data Type

INT64, except in the following cases:

  • If part is DATE, the function returns a DATE object.

Examples

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

WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input

/*-------------+--------------------*
 | the_day_utc | the_day_california |
 +-------------+--------------------+
 | 25          | 24                 |
 *-------------+--------------------*/

In the following example, EXTRACT returns values corresponding to different time parts from a column of type TIMESTAMP.

WITH Timestamps AS (
  SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
  SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
  timestamp_value,
  EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
  EXTRACT(YEAR FROM timestamp_value) AS year,
  EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+---------+---------+------+------*
 | timestamp_value         | isoyear | isoweek | year | week |
 +-------------------------+---------+---------+------+------+
 | 2005-01-03 12:34:56 UTC | 2005    | 1       | 2005 | 1    |
 | 2007-12-31 12:00:00 UTC | 2008    | 1       | 2007 | 52   |
 | 2009-01-01 12:00:00 UTC | 2009    | 1       | 2009 | 0    |
 | 2009-12-31 12:00:00 UTC | 2009    | 53      | 2009 | 52   |
 | 2017-01-02 12:00:00 UTC | 2017    | 1       | 2017 | 1    |
 | 2017-05-26 12:00:00 UTC | 2017    | 21      | 2017 | 21   |
 *-------------------------+---------+---------+------+------*/

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

WITH table AS (SELECT TIMESTAMP("2017-11-05 00:00:00+00") AS timestamp_value)
SELECT
  timestamp_value,
  EXTRACT(WEEK(SUNDAY) FROM timestamp_value) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp_value) AS week_monday
FROM table;

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------+---------------*
 | timestamp_value         | week_sunday | week_monday   |
 +-------------------------+-------------+---------------+
 | 2017-11-05 00:00:00 UTC | 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 parts for 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 a TIMESTAMP object.

format_string contains the format elements that define how timestamp_string is formatted. Each element in timestamp_string must have a corresponding element in format_string. The location of each element in format_string must match the location of each element in 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-01 00:00:00.0. This initialization value uses the time zone specified by the function's time zone argument, if present. If not, the initialization value uses the default time zone, UTC. For instance, if the year is unspecified then it defaults to 1970, and so on.
  • Case insensitivity. Names, such as Monday, February, and so on, are case insensitive.
  • Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the timestamp string. In addition, leading and trailing white spaces in the timestamp string are always allowed, even if they are not in the format string.
  • Format precedence. When two (or more) format elements have overlapping information (for example both %F and %Y affect the year), the last one generally overrides any earlier ones, with some exceptions (see the descriptions of %s, %C, and %y).
  • Format divergence. %p can be used with am, AM, pm, and PM.

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 optional parameter to specify a time zone. See Time zone definitions for information on 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 a timestamp. string_expression must include a timestamp literal. If string_expression includes a time zone in the timestamp literal, do not include an explicit time_zone argument.
  • date_expression[, time_zone]: Converts a date to a timestamp. The value returned is the earliest timestamp that falls within the given date.
  • datetime_expression[, time_zone]: Converts a datetime to a timestamp.

This function supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used.

Return Data Type

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 of any time zone.

TIMESTAMP_ADD supports the following values for date_part:

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

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(timestamp_expression_a, timestamp_expression_b, date_part)

Description

Returns the whole number of specified date_part intervals between two timestamps (timestamp_expression_a - timestamp_expression_b). If the first timestamp is earlier than the second one, the output is negative. Produces an error if the computation overflows the result type, such as if the difference in microseconds between the two timestamps would overflow an INT64 value.

TIMESTAMP_DIFF supports the following values for date_part:

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

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 the second 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 specified HOUR 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-01 00: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-01 00: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:00 UTC 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:

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

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, date_time_part[, time_zone])

Description

Truncates a timestamp to the granularity of date_time_part. The timestamp is always rounded to the beginning of date_time_part, which can be one of the following:

  • MICROSECOND: If used, nothing is truncated from the value.
  • MILLISECOND: The nearest lessor or equal millisecond.
  • SECOND: The nearest lessor or equal second.
  • MINUTE: The nearest lessor or equal minute.
  • HOUR: The nearest lessor or equal hour.
  • DAY: The day in the Gregorian calendar year that contains the TIMESTAMP value.
  • WEEK: The first day of the week in the week that contains the TIMESTAMP value. Weeks begin on Sundays. WEEK is equivalent to WEEK(SUNDAY).
  • WEEK(WEEKDAY): The first day of the week in the week that contains the TIMESTAMP value. Weeks begin on WEEKDAY. WEEKDAY must be one of the following: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, or SATURDAY.
  • ISOWEEK: The first day of the ISO 8601 week in the ISO week that contains the TIMESTAMP value. The ISO week begins on Monday. The first ISO week of each ISO year contains the first Thursday of the corresponding Gregorian calendar year.
  • MONTH: The first day of the month in the month that contains the TIMESTAMP value.
  • QUARTER: The first day of the quarter in the quarter that contains the TIMESTAMP value.
  • YEAR: The first day of the year in the year that contains the TIMESTAMP value.
  • ISOYEAR: The first day of the ISO 8601 week-numbering year in the ISO year that contains the TIMESTAMP value. The ISO year is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

TIMESTAMP_TRUNC function supports an optional time_zone parameter. This parameter applies to the following date_time_part:

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>)
  • ISOWEEK
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR

Use this parameter if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation.

When truncating a timestamp to MINUTE orHOUR parts, TIMESTAMP_TRUNC determines the civil time of the timestamp in the specified (or default) time zone and 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 is non-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 second column shows the output of TIMESTAMP_TRUNC using weeks that start on Monday. Because the timestamp_expression falls on a Sunday in UTC, TIMESTAMP_TRUNC truncates it to the preceding Monday. The third column shows the same function with the optional Time zone definition argument 'Pacific/Auckland'. Here, the function truncates the timestamp_expression using New Zealand Daylight Time, where it falls on a Monday.

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_truncated
FROM (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 the Gregorian calendar year 2015. However, TIMESTAMP_TRUNC with the ISOYEAR date part truncates the timestamp_expression to the beginning of the ISO year, not the Gregorian calendar year. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29. Therefore the ISO year boundary preceding the timestamp_expression 2015-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. Truncates higher 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 higher levels 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 time zone. However, when a timestamp value is displayed, it is usually converted to a 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 the TIMESTAMP; it is only a human-understandable way to describe the point in time that the timestamp represents.

Some timestamp functions have a time zone argument. A time zone is needed to convert between civil time (YYYY-MM-DD HH:MM:SS) and the absolute time represented by a timestamp. A function like PARSE_TIMESTAMP takes an input string that represents a civil time and returns a timestamp that represents an absolute time. A time zone is needed for this conversion. A function like EXTRACT takes an input timestamp (absolute time) and converts it to civil time in order to extract 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 zone and specify a different one. You can specify a time zone by either supplying the 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, see Time zones.