TIME_TRUNC

Definition

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

Where part can be any of the following:

TIME_TRUNC(time_expression, part)

• MICROSECOND
• MILLISECOND

Returns: TIME

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
  CAST('10:00:44' AS TIME) AS original,
  TIME_TRUNC(CAST('10:00:44' AS TIME), HOUR) AS truncated

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.

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

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

• TIMESTAMP_TRUNC
• Dates and Times in Standard SQL