TIME_TRUNC

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

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

• 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.

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

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

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

• EXTRACT

• Dates and Times in Standard SQL