See also DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC

Definition

The TIMESTAMP_TRUNC function in BigQuery will truncate the timestamp to the given date_part.

Where date_part can be any of the following:

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])

MICROSECOND
MILLISECOND
WEEK(): Truncates date_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 date_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.
ISOYEAR: Truncates date_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 also supports the timezone parameter for the following date_parts:

SELECT
  CAST('2021-01-01 02:44:00 Europe/Berlin' AS TIMESTAMP) AS original_timestamp,
  TIMESTAMP_TRUNC(CAST('2021-01-01 02:44:00 Europe/Berlin' AS TIMESTAMP), HOUR) AS hour,
  TIMESTAMP_TRUNC(CAST('2021-01-01 02:44:00 Europe/Berlin' AS TIMESTAMP), MINUTE) AS minute,
  TIMESTAMP_TRUNC(CAST('2021-01-01 02:44:00 Europe/Berlin' AS TIMESTAMP), YEAR) AS year

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

Returns: TIMESTAMP

original_timestamp	hour	minute	year
2021-01-01T01:44:00.000Z	2021-01-01T01:00:00.000Z	2021-01-01T01:44:00.000Z	2021-01-01T00:00:00.000Z

Practical Info

Always returns a TIMESTAMP, so even if you are truncating to the YEAR, you'll receive back the first day of that year at midnight UTC. For that reason, it's often use in conjunction with FORMAT_TIMESTAMP if you want to just use the year YYYY.
The timestamp parameter is optional and will default to UTC if not specified.

Common Questions

How do I aggregate my data by hour including timestamp?

TIMESTAMP_TRUNC is very handy for aggregating your data by a particular date_part, like HOUR, while also accounting for different timestamps. See the example below to see how you can aggregate by HOUR:

SELECT
  SUM(number) AS total,
  TIMESTAMP_TRUNC(date, HOUR) AS hour
FROM
  (
    SELECT
      CAST('2021-02-04 12:50:01-7:00' AS TIMESTAMP) AS date,
      3 AS number
    UNION ALL
(    SELECT
      CAST('2021-02-04 17:42:41+1:00' AS TIMESTAMP) AS date,
      7 AS number)
    UNION ALL
(    SELECT
      CAST('2021-02-04 17:01:00 Europe/Madrid' AS TIMESTAMP) AS date,
      27 AS number)
  ) AS table_3
GROUP BY
  hour

total	hour
3	2021-02-04T19:00:00.000Z
34	2021-02-04T16:00:00.000Z

Make sure you've included your TIMESTAMP_TRUNC column in your GROUP BY!

How do I convert a timestamp to a different timezone?

TIMESTAMP('2021-02-04 12:50:01','Australia/Sydney') 
------------
2021-02-04 01:50:01 UTC

The TIMESTAMP function allows you to convert a string to a TIMESTAMP given a certain timezone.

3. How do I convert a timestamp to seconds?

TIMESTAMP_TRUNC('2015-12-04 12:05:33',SECOND) second

To convert a TIMESTAMP to seconds, or any date_part, we can use TIMESTAMP_TRUNC:

SELECT
  '2015-12-04 12:05:33' AS original_ts,
  TIMESTAMP_TRUNC(CAST('2015-12-04 12:05:33' AS TIMESTAMP), SECOND) AS truncated,
  FORMAT_TIMESTAMP('%S', CAST('2015-12-04 12:05:33' AS TIMESTAMP)) AS formatted,
  EXTRACT(SECOND FROM CAST('2015-12-04 12:05:33' AS TIMESTAMP)) AS extracted

You can also use FORMAT_TIMESTAMP or EXTRACT to format your results differently:

original_ts	truncated	formatted	extracted
2015-12-04 12:05:33	2015-12-04T12:05:33.000Z	33	33

The difference is the types that are returned:

TIMESTAMP_TRUNC -> TIMESTAMP
FORMAT_TIMESTAMP -> STRING
EXTRACT -> INT64

Troubleshooting Common Errors

Argument 1 of TIMESTAMP_TRUNC has incorrect type: expected timestamp found date.

You'll need to make sure you're using the right function for your data type. To use TIMESTAMP_TRUNC, you must be working with a TIMESTAMP, and not a DATE, DATETIME, or TIME.

You can use CAST to change your other date types to TIMESTAMP, or use one of the equivalent functions for other date types like DATE_TRUNC.

DATETIME_TRUNC
Dates and Times in Standard SQL

TIMESTAMP_TRUNC