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
original | truncated |
---|---|
10:00:44 | 10:00:00 |
Practical Info
- Always returns a
TIME
so if you want to do any arithmetic you'll need to continue to useTIME
functions likeTIME_DIFF
. Or you can useEXTRACT
to convert to anINT64
.
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...
total | hour |
---|---|
3 | 12:00:00 |
7 | 07:00:00 |
27 | 17: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
.
Related Pages
- TIMESTAMP_TRUNC
- Dates and Times in Standard SQL