SQL Resources
SQL
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.

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
    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:

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

    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
  • EXTRACT
  • Dates and Times in Standard SQL
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.