Timestamp Functions

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP() Returns the current timestamp. As the timestamp refers to an absolute moment in time, no time zone argument is necessary (or allowed). The returned value is of the timestamp type.

SELECT CURRENT_TIMESTAMP()

'2019-08-19T18:19:06.705792Z'

SELECT TYPEOF(CURRENT_TIMESTAMP())

'timestamp'

SELECT CAST(CURRENT_TIMESTAMP() AS string)

'2019-08-19T18:19:06.705792Z'

SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP())

'2019-Aug-19 18:19:06'

Here are some examples of comparison and interval arithmetic operations on the timestamp type:

SELECT CURRENT_TIMESTAMP() > PARSE_TIMESTAMP('%Y/%m/%d %H:%M', '2019/08/17 10:10')

true

SELECT CURRENT_TIMESTAMP() > CURRENT_TIMESTAMP() - INTERVAL 1 SECOND

true

SELECT
    CURRENT_TIMESTAMP() AS now,
    CURRENT_TIMESTAMP() - INTERVAL 1 HOUR AS one_hour_ago

+--------------------------------+-------------------------------+ | now | one_hour_ago | |--------------------------------+-------------------------------+ | '2019-08-19T18:19:06.705792Z' | '2019-08-19T17:19:06.705792Z' | +--------------------------------+-------------------------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format, timestamp[, timezone] Converts timestamp to string using the given format, as of the given timezone (default UTC).

The format specification is the standard strftime with CCTZ extensions.

Note that FORMAT_TIMESTAMP also works for arguments of date, time, and timestamp types, and thus can be used as a generic "format any datetime-like type" function.

SELECT FORMAT_TIMESTAMP('%Y/%m/%d %H:%M:%S', TIMESTAMP '2019-08-19T18:19:06Z')

'2019/08/19 18:19:06'

SELECT FORMAT_TIMESTAMP('%Y/%m/%d %H:%M:%S', DATETIME '2019-08-19 18:19:06')

'2019/08/19 18:19:06'

SELECT FORMAT_TIMESTAMP('%Y/%m/%d %H:%M:%S', DATE '2019-08-19')

'2019/08/19 00:00:00'

SELECT FORMAT_TIMESTAMP('%Y/%m/%d %H:%M:%S', TIME '18:19:06')

'1970/01/01 18:19:06'

FORMAT_ISO8601 timestamp

FORMAT_ISO8601(timestamp[, timezone])

Converts a timestamp to string using the ISO 8601 extended format. The default timezone is 'UTC'. We always include a timezone offset in the output string (or a Z suffix for UTC).

SELECT FORMAT_ISO8601(CURRENT_TIMESTAMP(), 'America/Los_Angeles')

'2019-08-19T11:19:06.705792-7:00'

SELECT FORMAT_ISO8601(CURRENT_TIMESTAMP())

'2019-08-19T18:19:06.705792Z'

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format, string[, default_timezone]) Parses the date string (formatted using the given format) into a timestamp value. If the format string does not contain a timezone, default_timezone is used (UTC if not specified).

The format specification is the standard strptime with CCTZ extensions.

SELECT CURRENT_TIMESTAMP()

'2019-08-19T18:19:06.705792Z'

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E*S', '2019-08-19 18:19:06.705792')

'2019-08-19T18:19:06.705792Z'

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E*S %Ez', '2019-08-19 11:19:06.705792 -07:00')

'2019-08-19T18:19:06.705792Z'

SELECT TYPEOF(PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E*S', '2019-08-19 18:19:06.705792'))

'timestamp'

Here are some examples of comparison and interval arithmetic operations on the timestamp type:

SELECT CURRENT_TIMESTAMP() > PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-17 18:19')

true

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') > PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') - INTERVAL 1 SECOND

true

SELECT
    PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') AS today,
    (PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') - INTERVAL 7 DAY) AS last_week

+------------------------------+-----------------------------+ | today | last_week | |------------------------------+-----------------------------+ | 2019-08-19T18:19:00.000000Z | 2019-08-12T18:19:00.000000Z | +------------------------------+-----------------------------+

PARSE_TIMESTAMP_ISO8601

PARSE_TIMESTAMP_ISO8601(string) Parses the timestamp from an ISO 8601 string. The string must include a timezone offset (or the Z suffix for UTC).

SELECT CURRENT_TIMESTAMP()

'2019-08-19T18:19:06.705792Z'

SELECT PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z')

'2019-08-19T18:19:06.705792Z'

SELECT PARSE_TIMESTAMP_ISO8601('2019-08-19T11:19:06.705792-7')

'2019-08-19T18:19:06.705792Z'

SELECT TYPEOF(PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z'))

'timestamp'

Here are some examples of comparison and interval arithmetic operations on the timestamp type:

SELECT CURRENT_TIMESTAMP() > PARSE_TIMESTAMP_ISO8601('2019-08-17T18:19:06Z')

true

SELECT PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z') > PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z') - INTERVAL 1 SECOND

true

SELECT
    PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:00Z') AS today,
    (PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:00Z') - INTERVAL 7 DAY) AS last_week

TIME_BUCKET

TIME_BUCKET(interval, timestamp[, origin]) Truncates timestamp to the largest multiple of interval smaller than or equal to timestamp.

SELECT TIME_BUCKET(SECONDS(30), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T10:30:00.000000Z'

SELECT TIME_BUCKET(MINUTES(20), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T10:20:00.000000Z'

SELECT TIME_BUCKET(HOURS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T10:00:00.000000Z'

SELECT TIME_BUCKET(DAYS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T00:00:00.000000Z'

SELECT TIME_BUCKET(MONTHS(2), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-01T00:00:00.000000Z'

SELECT TIME_BUCKET(MONTHS(3), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-04-01T00:00:00.000000Z'

SELECT TIME_BUCKET(MONTHS(18), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-01-01T00:00:00.000000Z'

SELECT TIME_BUCKET(YEARS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-01-01T00:00:00.000000Z'

SELECT TIME_BUCKET(YEARS(5), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2015-01-01T00:00:00.000000Z'

SELECT TIME_BUCKET(YEARS(1000), TIMESTAMP '2018-05-26T10:30:20.345Z')

'1970-01-01T00:00:00.000000Z'

The optional origin parameter is of type timestamp.It allows you to specify how the time buckets should be aligned. The origin parameter is only supported if interval is a microsecond interval, and not if it is a month interval (i.e. MONTHS(1) or YEARS(1)). If origin is not specified, 1970-01-01T00:00:00.000Z (UTC) is used. When bucketizing on a month interval, the origin is always 1970-01-01T00:00:00.000Z.

SELECT
    TIME_BUCKET(
        DAYS(1),
        TIMESTAMP '2018-05-26T10:30:20.345Z',
        TIMESTAMP '2016-01-26T06:30:00.000Z'
    )

'2018-05-26T06:30:00.000000Z'

SELECT
    TIME_BUCKET(
        DAYS(1),
        TIMESTAMP '2018-05-26T10:30:20.345Z',
        TIMESTAMP '2023-01-26T06:30:00.000Z'
    )

'2018-05-26T06:30:00.000000Z'

TIMESTAMP_MICROS

TIMESTAMP_MICROS(n) Constructs value of type timestamp from n microseconds since the Unix epoch.

SELECT TIMESTAMP_MICROS(1566341708000000)

'2019-08-20T22:55:08.000000Z'

SELECT TYPEOF(TIMESTAMP_MICROS(1566341708000000))

'timestamp'

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(n) Constructs value of type timestamp from n milliseconds since the Unix epoch.

SELECT TIMESTAMP_MILLIS(1566341708000.5)

'2019-08-20T22:55:08.000500Z'

SELECT TYPEOF(TIMESTAMP_MILLIS(1566341708000))

'timestamp'

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(n) Constructs value of type timestamp from n seconds since the Unix epoch.

SELECT TIMESTAMP_SECONDS(1566341708)

'2019-08-20T22:55:08.000000Z'

SELECT TIMESTAMP_SECONDS(1566341708.5)

'2019-08-20T22:55:08.500000Z'

SELECT TYPEOF(TIMESTAMP_SECONDS(1566341708))

'timestamp'

UNIX_MICROS

UNIX_MICROS(ts) Returns the value of the timestamp ts as an int number of microseconds since the Unix epoch.

SELECT UNIX_MICROS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708000000

UNIX_MILLIS

UNIX_MILLIS(ts) Returns the value of the timestamp ts as an int number of milliseconds since the Unix epoch.

SELECT UNIX_MILLIS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708000

UNIX_SECONDS

UNIX_SECONDS(ts) Returns the value of the timestamp ts as an int number of seconds since the Unix epoch.

SELECT UNIX_SECONDS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708

AT TIME ZONE (timestamp)

timestamp AT TIME ZONE timezone

Returns the datetime in the given time zone at a specific timestamp.

SELECT CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles'

2019-08-19T11:19:06.705792

SELECT TYPEOF(CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles')

'datetime'

Timezones can also be chained to obtain a datetime in a different timezone as follows:

SELECT
    CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles'
    AT TIME ZONE 'America/New_York'

'2019-08-19T14:19:06.705792'

EXTRACT timestamp

EXTRACT(part FROM timestamp)

Extracts a component specified by part from timestamp.

Here are supported values for part for timestamp along with example queries.

Type	Part	Query	Result
TIMESTAMP	MICROSECOND	`SELECT EXTRACT(MICROSECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`345000`
TIMESTAMP	MILLISECOND	`SELECT EXTRACT(MILLISECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`345`
TIMESTAMP	SECOND	`SELECT EXTRACT(SECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`20`
TIMESTAMP	MINUTE	`SELECT EXTRACT(MINUTE FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`30`
TIMESTAMP	HOUR	`SELECT EXTRACT(HOUR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`4`
TIMESTAMP	DAY	`SELECT EXTRACT(DAY FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`26`
TIMESTAMP	DAYOFWEEK	`SELECT EXTRACT(DAYOFWEEK FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`7`
TIMESTAMP	DOW	`SELECT EXTRACT(DOW FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`7`
TIMESTAMP	DAYOFYEAR	`SELECT EXTRACT(DAYOFYEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`146`
TIMESTAMP	DOY	`SELECT EXTRACT(DOY FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`146`
TIMESTAMP	MONTH	`SELECT EXTRACT(MONTH FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`5`
TIMESTAMP	QUARTER	`SELECT EXTRACT(QUARTER FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`2`
TIMESTAMP	YEAR	`SELECT EXTRACT(YEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`2018`
TIMESTAMP	DATE	`SELECT EXTRACT(DATE FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`2018-05-26`
TIMESTAMP	TIME	`SELECT EXTRACT(TIME FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`04:30:20.345`
TIMESTAMP	EPOCH	`SELECT EXTRACT(EPOCH FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`1527309020`
TIMESTAMP	ISOWEEK	`SELECT EXTRACT(ISOWEEK FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`21`
TIMESTAMP	ISOYEAR	`SELECT EXTRACT(ISOYEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')`	`2018`

DATE_TRUNC (timestamp)

DATE_TRUNC(precision, timestamp)

Truncates the provided timestamp (assumed at timezone UTC) to the precision level provided. To use an alternate timezone use AT TIME ZONE.

The precision may be specified as a string (see examples below), a microsecond interval (INTERVAL x MICROSECOND, INTERVAL x DAY, etc), or a moth interval (INTERVAL x MONTH, INTERVAL x YEAR).

Here are supported values for precision (case-insensitive) along with example queries:

Type	Precision	Query	Result
TIMESTAMP	MILLENNIUM	`SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1000-01-01T00:00:00.000000Z`
TIMESTAMP	CENTURY	`SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1900-01-01T00:00:00.000000Z`
TIMESTAMP	DECADE	`SELECT DATE_TRUNC('DECADE', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1990-01-01T00:00:00.000000Z`
TIMESTAMP	YEAR	`SELECT DATE_TRUNC('YEAR', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-01-01T00:00:00.000000Z`
TIMESTAMP	QUARTER	`SELECT DATE_TRUNC('QUARTER', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-10-01T00:00:00.000000Z`
TIMESTAMP	MONTH	`SELECT DATE_TRUNC('MONTH', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-01T00:00:00.000000Z`
TIMESTAMP	WEEK	`SELECT DATE_TRUNC('WEEK', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-14T00:00:00.000000Z`
TIMESTAMP	DAY	`SELECT DATE_TRUNC('DAY', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T00:00:00.000000Z`
TIMESTAMP	HOUR	`SELECT DATE_TRUNC('HOUR', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:00:00.000000Z`
TIMESTAMP	MINUTE	`SELECT DATE_TRUNC('MINUTE', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:13:00.000000Z`
TIMESTAMP	SECOND	`SELECT DATE_TRUNC('SECOND', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:13:56.000000Z`
TIMESTAMP	MILLISECONDS	`SELECT DATE_TRUNC('MILLISECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:13:56.123000Z`
TIMESTAMP	MICROSECONDS	`SELECT DATE_TRUNC('MICROSECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:13:56.123456Z`
TIMESTAMP	microsecond_interval	`SELECT DATE_TRUNC(INTERVAL 1 SECOND, TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-20T17:13:56.000000Z`
TIMESTAMP	month_interval	`SELECT DATE_TRUNC(INTERVAL 1 MONTH, TIMESTAMP '1999-11-20T17:13:56.123456Z')`	`1999-11-01T00:00:00.000000Z`

DATE_DIFF (timestamp)

DATE_DIFF(precision, start_timestamp, end_timestamp)

Computes the difference between start_timestamp and end_timestamp at the precision level provided. The difference is truncated to the nearest integer.

Here are supported values for precision (case-insensitive) along with example queries:

Note: A MONTH is equal to 30 DAYs. A QUARTER is equal to 90 DAYs. A YEAR is equal to 365 DAYs.

Type	Precision	Query	Result
TIMESTAMP	MILLENNIUM	`SELECT DATE_DIFF('MILLENNIUM', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')`	`1`
TIMESTAMP	CENTURY	`SELECT DATE_DIFF('CENTURY', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')`	`10`
TIMESTAMP	DECADE	`SELECT DATE_DIFF('DECADE', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')`	`100`
TIMESTAMP	YEAR	`SELECT DATE_DIFF('YEAR', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')`	`1000`
TIMESTAMP	QUARTER	`SELECT DATE_DIFF('QUARTER', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2000-11-20T17:13:56.123456Z')`	`4`
TIMESTAMP	MONTH	`SELECT DATE_DIFF('MONTH', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2000-11-20T17:13:56.123456Z')`	`12`
TIMESTAMP	WEEK	`SELECT DATE_DIFF('WEEK', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-12-20T17:13:56.123456Z')`	`4`
TIMESTAMP	DAY	`SELECT DATE_DIFF('DAY', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-28T17:13:56.123456Z')`	`8`
TIMESTAMP	HOUR	`SELECT DATE_DIFF('HOUR', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-19T17:13:56.123456Z')`	`-24`
TIMESTAMP	MINUTE	`SELECT DATE_DIFF('MINUTE', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:23:56.123456Z')`	`10`
TIMESTAMP	SECOND	`SELECT DATE_DIFF('SECOND', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:12:56.123456Z')`	`-60`
TIMESTAMP	MILLISECONDS	`SELECT DATE_DIFF('MILLISECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:13:57.123456Z')`	`1000`
TIMESTAMP	MICROSECONDS	`SELECT DATE_DIFF('MICROSECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:13:56.123457Z')`	`1`