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