SQL Resources/BigQuery/TIME_TRUNC

TIME_TRUNC

Definition

The TIME_TRUNC function in BigQuery will truncate the time to the given date_part.

Syntax

TIME_TRUNC(time_expression, part)

Where part can be any of the following:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Returns: TIME

SELECT
  CAST('10:00:44' AS TIME) AS original,
  TIME_TRUNC(CAST('10:00:44' AS TIME), HOUR) AS truncated
original
10:00:44
truncated
10:00:00

Practical Info

  • Always returns a TIME so if you want to do any arithmetic you'll need to continue to use TIME functions like TIME_DIFF. Or you can use EXTRACT to convert to an INT64.

Common Questions

How do I aggregate my data by hour?

TIME_TRUNC is very handy for aggregating your data by a particular date_part, like HOUR. See the example below to see how you can aggregate by HOUR:

SELECT
  SUM(number) AS total,
  TIME_TRUNC(time, HOUR) AS hour
FROM
  (
    SELECT
      CAST('12:50:01' AS TIME) AS time,
      3 AS number
    UNION ALL
(    SELECT
      CAST('07:42:41' AS TIME) AS time,
      7 AS number)
    UNION ALL
(    SELECT
      CAST('17:01:00' AS TIME) AS time,
      27 AS number)
  ) AS table_3
GROUP BY
  hour
total
3
7
27
hour
12:00:00
07:00:00
17:00:00

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

Troubleshooting Common Errors

Argument 1 of TIME_TRUNC has incorrect type: expected time found date.

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

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

Related Pages

Try a better way to write SQL in BigQuery
Write SQL together with Count.
Try a better way to write SQL in BigQuery
Write SQL together with Count.