TIMESTAMP_TRUNC

See also DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC

Definition

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

Syntax

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])

Where date_part can be any of the following:

TIMESTAMP_TRUNC also supports the timezone parameter for the following date_parts:

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

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

Practical Info

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

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?

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

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

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

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

The difference is the types that are returned:

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.

Related Pages