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
.
Syntax
DATETIME_TRUNC(datetime_expression, date_part)
Where date_part
can be any of the following:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
WEEK()
: Truncates date_expression
to the preceding week boundary, where weeks begin on WEEKDAY
. Valid values for WEEKDAY
are SUNDAY
, MONDAY
, TUESDAY
, WEDNESDAY
, THURSDAY
, FRIDAY
, and SATURDAY
.ISOWEEK
: Truncates date_expression
to the preceding ISO 8601 week boundary. ISOWEEK
s begin on Monday. The first ISOWEEK
of each ISO year contains the first Thursday of the corresponding Gregorian calendar year. Any date_expression
earlier than this will truncate to the preceding Monday.MONTH
QUARTER
YEAR
ISOYEAR
: Truncates date_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
SELECT
DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), MILLISECOND) AS month,
DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), MINUTE) AS minute,
DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), YEAR) AS year
Practical Info
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 with FORMAT_DATETIME
if you want to just use the year YYYY
. 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
:
SELECT
SUM(number) AS total,
DATETIME_TRUNC(date, MONTH) AS month
FROM
(
SELECT
CAST('2021-02-04 12:50:01' AS DATETIME) AS date,
3 AS number
UNION ALL
( SELECT
CAST('2021-02-14 00:42:41' AS DATETIME) AS date,
7 AS number)
UNION ALL
( SELECT
CAST('2021-01-04 17:01:00' AS DATETIME) AS date,
27 AS number)
) AS table_3
GROUP BY
month
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.
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
.