SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='time-trunc'

TIME_TRUNC

See also DATE_TRUNC, DATETIME_TRUNC, TIMESTAMP_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

originaltruncated
10:00:4410: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:

Loading code...
totalhour
312:00:00
707:00:00
2717: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.

Loading code...

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.

  • TIMESTAMP_TRUNC
  • Dates and Times in Standard SQL