DATE_TRUNC
Overview
The DATE_TRUNC()
function truncates a timestamp, timestamp with time zone or interval value to the specified precision,
effectively rounding down the value to the start of the given time unit. The return type matches the input type.
Syntax
The syntax for using the DATE_TRUNC()
function is as follows:
Parameters
field
: The unit of time used to truncate thesource
value. It acceptstext
inputs and is case-insensitivesource
: The value you want to truncate. It can beINTERVAL
,TIMESTAMP
orTIMESTAMP WITH TIME ZONE
time_zone
(applicable for the second syntax option): The time zone for the operation. It acceptstext
input
Fields
Below is a list of supported values to specify the fields param in DATE_TRUNC()
syntax.
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium
Some fields like microseconds
and milliseconds
are supported only for interval types.
Examples
Truncating to Year
This example truncates the timestamp to the year level.
The timestamp **“1911-12-02 19:40:00” **has been truncated to 1911, with the month and day set to January 1st.
Truncating to Day
This query truncates the timestamp “1911-12-02 19:40:00” to the day level.
The timestamp has been truncated to the same day, year, month, and day components.
Truncating to Week
This query truncates the timestamp “1911-12-02 19:40:00” to the week level.
The timestamp has been truncated to the start of the week containing the date, which is Monday, November 27, 1911, at 00:00:00.
Truncating to Quarter
This query truncates the timestamp “1911-12-02 19:40:00” to the quarter level.
The timestamp is truncated to the start of the quarter. The month and day are set to the first month and first day of the quarter, with time components reset to zero.
Truncating to Hour
This query truncates the interval “15 hours 10 minutes” to the hour precision.
The minutes and seconds components are set to zero, resulting in an interval of exactly 15 hours.
Truncating to Quarter (Interval)
This query truncates the interval “16 years 4 months” to the quarter-year level.
The interval is truncated to the nearest quarter-year unit. The months components is adjusted to the start of the quarter. Since each quarter consists of 3 months, 4 months is truncated down to 3 months, resulting in: