SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='datetime-trunc'
DATETIME_TRUNC
See also DATE_TRUNC, TIMESTAMP_TRUNC, TIME_TRUNC
Definition
The DATETIME_TRUNC
function in BigQuery will truncate the datetime to the given date_part
.
Where date_part
can be any of the following:
DATETIME_TRUNC(datetime_expression, date_part)
MICROSECOND
MILLISECOND
WEEK()
: Truncatesdate_expression
to the preceding week boundary, where weeks begin onWEEKDAY
. Valid values forWEEKDAY
areSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, andSATURDAY
.ISOWEEK
: Truncatesdate_expression
to the preceding ISO 8601 week boundary.ISOWEEK
s begin on Monday. The firstISOWEEK
of each ISO year contains the first Thursday of the corresponding Gregorian calendar year. Anydate_expression
earlier than this will truncate to the preceding Monday.ISOYEAR
: Truncatesdate_expression
to the preceding ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
Returns: DATETIME
month | minute | year |
---|---|---|
2021-01-01T02:44:00 | 2021-01-01T02:44:00 | 2021-01-01T00:00:00 |
Practical Info
- Always returns a
DATETIME
, so even if you are truncating to the YEAR, you'll receive back the first day of that year at midnight. For that reason, it's often use in conjunction withFORMAT_DATETIME
if you want to just use the yearYYYY
.
Common Questions
How do I aggregate my data by month?
DATETIME_TRUNC
is very handy for aggregating your data by a particular date_part
, like MONTH
. See the example below to see how you can aggregate by MONTH
:
Loading code...
total | month |
---|---|
10 | 2021-02-01T00:00:00 |
27 | 2021-01-01T00:00:00 |
Make sure you've included your DATETIME_TRUNC
column in your GROUP BY
!
Troubleshooting Common Errors
Argument 1 of DATETIME_TRUNC has incorrect type: expected datetime found timestamp.
Loading code...
You'll need to make sure you're using the right function for your data type. To use DATETIME_TRUNC
, you must be working with a DATETIME
, and not a DATE
, TIMESTAMP
, or TIME
.
You can use CAST
to change your other date types to DATETIME
, or use one of the equivalent functions for other date types like DATE_TRUNC
.
Related Pages
- TIMESTAMP_TRUNC
- Dates and Times in Standard SQL