- Categories:
DATE_TRUNC¶
Truncates a DATE
, TIME
, or TIMESTAMP
to the specified precision.
Note that truncation is not the same as extraction. For example:
Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp.
Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.
Syntax¶
DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> )
Returns¶
The returned value is the same type as the input value.
For example, if the input value is a TIMESTAMP
, then the returned value is a TIMESTAMP
.
Usage Notes¶
date_or_time_part
must be one of the values listed in Supported Date and Time Parts.When
date_or_time_part
isweek
(or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see Calendar Weeks and Weekdays.date_or_time_expr
can be a date, time, or timestamp.
Examples¶
Truncate a date (from a timestamp) down to the year, month, and day:
SELECT TO_DATE('2015-05-08T23:39:20.123-07:00') AS "DATE1", DATE_TRUNC('YEAR', "DATE1") AS "TRUNCATED TO YEAR", DATE_TRUNC('MONTH', "DATE1") AS "TRUNCATED TO MONTH", DATE_TRUNC('DAY', "DATE1") AS "TRUNCATED TO DAY"; +------------+-------------------+--------------------+------------------+ | DATE1 | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY | |------------+-------------------+--------------------+------------------| | 2015-05-08 | 2015-01-01 | 2015-05-01 | 2015-05-08 | +------------+-------------------+--------------------+------------------+
Truncate a TIMESTAMP
down to the hour, minute, and second. Note that in all cases, the returned value is a TIMESTAMP
,
but with zeros for the portions (e.g. fractional seconds) that were truncated:
SELECT TO_TIMESTAMP('2015-05-08T23:39:20.123-07:00') AS "TIMESTAMP1", DATE_TRUNC('HOUR', "TIMESTAMP1") AS "TRUNCATED TO HOUR", DATE_TRUNC('MINUTE', "TIMESTAMP1") AS "TRUNCATED TO MINUTE", DATE_TRUNC('SECOND', "TIMESTAMP1") AS "TRUNCATED TO SECOND"; +-------------------------+-------------------------+-------------------------+-------------------------+ | TIMESTAMP1 | TRUNCATED TO HOUR | TRUNCATED TO MINUTE | TRUNCATED TO SECOND | |-------------------------+-------------------------+-------------------------+-------------------------| | 2015-05-08 23:39:20.123 | 2015-05-08 23:00:00.000 | 2015-05-08 23:39:00.000 | 2015-05-08 23:39:20.000 | +-------------------------+-------------------------+-------------------------+-------------------------+
Truncate a time down to the minute:
SELECT TO_TIME('23:39:20.123') AS "TIME1", DATE_TRUNC('MINUTE', "TIME1") AS "TRUNCATED TO MINUTE"; +----------+---------------------+ | TIME1 | TRUNCATED TO MINUTE | |----------+---------------------| | 23:39:20 | 23:39:00 | +----------+---------------------+
Contrast truncation with extraction:
SELECT DATE_TRUNC('QUARTER', '2019-08-01 12:34:56.789'::TIMESTAMP_NTZ) AS "TRUNCATED", EXTRACT( 'QUARTER', '2019-08-01 12:34:56.789'::TIMESTAMP_NTZ) AS "EXTRACTED"; +-------------------------+-----------+ | TRUNCATED | EXTRACTED | |-------------------------+-----------| | 2019-07-01 00:00:00.000 | 3 | +-------------------------+-----------+