Timestamp Functions in Standard SQL

BigQuery supports the following TIMESTAMP functions.

NOTE: These functions return a runtime error if overflow occurs; result values are bounded by the defined date and timestamp min/max values.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Description

Parentheses are optional. This function handles leap seconds by smearing them across a window of 20 hours around the inserted leap second. CURRENT_TIMESTAMP() produces a TIMESTAMP value that is continuous, non-ambiguous, has exactly 60 seconds per minute and does not repeat values over the leap second.

Supported Input Types

Not applicable

Result Data Type

TIMESTAMP

Example

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

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

Description

Returns an INT64 value that corresponds to the specified part from a supplied timestamp_expression.

Allowed part values are:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • 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.

See Timezone definitions for information on how to specify a time zone.

Return Data Type

Generally INT64 . Returns DATE if part is DATE.

Examples

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

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

+------------+
| the_day    |
+------------+
| 25         |
+------------+

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

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

In the following example, timestamp_expression falls on a Sunday. 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') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

Description

Converts a timestamp_expression to a STRING data type. Supports an optional parameter to specify a timezone. See Timezone definitions for information on how to specify a time zone.

Return Data Type

STRING

Example

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00-08        |
+-------------------------------+

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])
3. TIMESTAMP(datetime_expression[, timezone])

Description

  1. Converts a STRING expression to a TIMESTAMP data type.

  2. Converts a DATE object to a TIMESTAMP data type.

  3. Converts a DATETIME object to a TIMESTAMP data type.

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

Return Data Type

TIMESTAMP

Example

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
  CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;

+------------------------+------------------------+------------------------+
| timestamp_str          | timestamp_date         | timestamp_datetime     |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+

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

Return Data Types

TIMESTAMP

Example

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

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

Return Data Type

TIMESTAMP

Example

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

Description

Returns the number of whole specified date_part intervals between two timestamps. The first timestamp_expression represents the later date; if the first timestamp_expression is earlier than the second timestamp_expression, the output is negative. Throws 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.

Return Data Type

INT64

Example

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as later_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 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);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

Description

Truncates a timestamp to the granularity of date_part.

TIMESTAMP_TRUNC supports the following values for date_part:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): Truncates timestamp_expression to the preceding week boundary, where weeks begin on WEEKDAY. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  • ISOWEEK: Truncates timestamp_expression to the preceding ISO 8601 week boundary. ISOWEEKs begin on Monday. The first ISOWEEK of each ISO year contains the first Thursday of the corresponding Gregorian calendar year. Any date_expression earlier than this will truncate to the preceding Monday.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Truncates timestamp_expression to the preceding ISO 8601 week-numbering year boundary. The ISO year boundary 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_parts:

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

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

Return Data Type

TIMESTAMP

Examples

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

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 Timezone definition argument 'Pacific/Auckland'. Here the function truncates the timestamp_expression using New Zealand Daylight Time, where it falls on a Monday.

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 07:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

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;

+------------------------+----------------+
| isoyear_boundary       | isoyear_number |
+------------------------+----------------+
| 2014-12-29 00:00:00+00 | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

Description

Formats a timestamp according to the specified format_string.

See Supported Format Elements For TIMESTAMP for a list of format elements that this function supports.

Return Data Type

STRING

Example

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

Description

Uses a format_string and a string representation of a timestamp to return a TIMESTAMP object.

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 insensitive names. 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).

See Supported Format Elements For TIMESTAMP for a list of format elements that this function supports.

Return Data Type

TIMESTAMP

Example

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 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.

Return Data Type

TIMESTAMP

Example

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 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.

Return Data Type

TIMESTAMP

Example

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

Description

Interprets int64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.

Return Data Type

TIMESTAMP

Example

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

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.

Return Data Type

INT64

Example

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

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.

Return Data Type

INT64

Example

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

Description

Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.

Return Data Type

INT64

Example

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

Supported format elements for TIMESTAMP

Unless otherwise noted, TIMESTAMP functions that use format strings support the following elements:

Format element Description
%A The full weekday name.
%a The abbreviated weekday name.
%B The full month name.
%b or %h The abbreviated month name.
%C The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99).
%c The date and time representation.
%D The date in the format %m/%d/%y.
%d The day of the month as a decimal number (01-31).
%e The day of month as a decimal number (1-31); single digits are preceded by a space.
%F The date in the format %Y-%m-%d.
%G The ISO 8601 year with century as a decimal number.
%g The ISO 8601 year without century as a decimal number (00-99).
%H The hour (24-hour clock) as a decimal number (00-23).
%I The hour (12-hour clock) as a decimal number (01-12).
%j The day of the year as a decimal number (001-366).
%k The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.
%l The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.
%M The minute as a decimal number (00-59).
%m The month as a decimal number (01-12).
%n A newline character.
%P Either am or pm.
%p Either AM or PM.
%R The time in the format %H:%M.
%r The 12-hour clock time using AM/PM notation.
%S The second as a decimal number (00-60).
%s The number of seconds since 1970-01-01 00:00:00 UTC. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence.
%T The time in the format %H:%M:%S.
%t A tab character.
%U The week number of the year (Sunday as the first day of the week) as a decimal number (00-53).
%u The weekday (Monday as the first day of the week) as a decimal number (1-7).
%V The week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.
%W The week number of the year (Monday as the first day of the week) as a decimal number (00-53).
%w The weekday (Sunday as the first day of the week) as a decimal number (0-6).
%X The time representation in HH:MM:SS format.
%x The date representation in MM/DD/YY format.
%Y The year with century as a decimal number.
%y The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s.
%Z The time zone name.
%z The offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich.
%% A single % character.
%Ez RFC 3339-compatible numeric time zone (+HH:MM or -HH:MM).
%E#S Seconds with # digits of fractional precision.
%E*S Seconds with full fractional precision (a literal '*').
%E4Y Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year.

Timezone definitions

Certain date and timestamp functions allow you to override the default time zone and specify a different one. You can specify a timezone by supplying its UTC offset using the following format:

(+|-)H[H][:M[M]]

For example:

-08:00
Was this page helpful? Let us know how we did:

Send feedback about...