TIMESTAMP_TRUNC
See also DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC
Definition
The TIMESTAMP_TRUNC
function in BigQuery will truncate the timestamp to the given date_part
.
Where date_part
can be any of the following:
TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])
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.
TIMESTAMP_TRUNC
also supports the timezone
parameter for the following date_parts
:
Use this parameter if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation.
Returns: TIMESTAMP
original_timestamp | hour | minute | year |
---|---|---|---|
2021-01-01T01:44:00.000Z | 2021-01-01T01:00:00.000Z | 2021-01-01T01:44:00.000Z | 2021-01-01T00:00:00.000Z |
Practical Info
- Always returns a
TIMESTAMP
, so even if you are truncating to theYEAR
, you'll receive back the first day of that year at midnight UTC. For that reason, it's often use in conjunction withFORMAT_TIMESTAMP
if you want to just use the yearYYYY
. - The
timestamp
parameter is optional and will default to UTC if not specified.
Common Questions
How do I aggregate my data by hour including timestamp?
TIMESTAMP_TRUNC
is very handy for aggregating your data by a particular date_part
, like HOUR
, while also accounting for different timestamps. See the example below to see how you can aggregate by HOUR
:
total | hour |
---|---|
3 | 2021-02-04T19:00:00.000Z |
34 | 2021-02-04T16:00:00.000Z |
Make sure you've included your TIMESTAMP_TRUNC
column in your GROUP BY
!
How do I convert a timestamp to a different timezone?
The TIMESTAMP
function allows you to convert a string to a TIMESTAMP
given a certain timezone
.
3. How do I convert a timestamp to seconds?
TIMESTAMP_TRUNC('2015-12-04 12:05:33',SECOND) second
To convert a TIMESTAMP
to seconds, or any date_part
, we can use TIMESTAMP_TRUNC
:
You can also use FORMAT_TIMESTAMP
or EXTRACT
to format your results differently:
original_ts | truncated | formatted | extracted |
---|---|---|---|
2015-12-04 12:05:33 | 2015-12-04T12:05:33.000Z | 33 | 33 |
The difference is the types that are returned:
TIMESTAMP_TRUNC
->TIMESTAMP
FORMAT_TIMESTAMP
->STRING
EXTRACT
->INT64
Troubleshooting Common Errors
Argument 1 of TIMESTAMP_TRUNC has incorrect type: expected timestamp found date.
You'll need to make sure you're using the right function for your data type. To use TIMESTAMP_TRUNC
, you must be working with a TIMESTAMP
, and not a DATE
, DATETIME
, or TIME
.
You can use CAST
to change your other date types to TIMESTAMP
, or use one of the equivalent functions for other date types like DATE_TRUNC
.
Related Pages
- DATETIME_TRUNC
- Dates and Times in Standard SQL