DATE_TRUNC()

DATE_TRUNC() truncates a temporal value to the start of a specified calendar or clock unit.

Description

DATE_TRUNC(unit, date) returns the input value rounded down to the beginning of the specified unit. The unit name is case-insensitive. week truncates to Monday at midnight.

The supported units depend on the input type:

Input type	Supported units
DATE	year, quarter, month, week, day
DATETIME or TIMESTAMP	year, quarter, month, week, day, hour, minute, second

If either argument is NULL, the result is NULL. An unsupported unit or input type is an error.

Syntax

DATE_TRUNC(unit, date)

Arguments

Argument	Description
unit	A case-insensitive string naming a supported time unit.
date	A DATE, DATETIME, or TIMESTAMP value to truncate.

Return Value

Returns the truncated value in the temporal type family of the input.

Examples

DROP DATABASE IF EXISTS date_trunc_demo;
CREATE DATABASE date_trunc_demo;
USE date_trunc_demo;

SELECT DATE_TRUNC('year', CAST('2024-05-16 12:34:56' AS DATETIME)) AS datetime_year;
SELECT DATE_TRUNC('hour', CAST('2024-05-16 12:34:56' AS DATETIME)) AS datetime_hour;
SELECT DATE_TRUNC('second', CAST('2024-05-16 12:34:56' AS TIMESTAMP)) AS timestamp_second;
SELECT DATE_TRUNC('week', CAST('2024-05-19' AS DATE)) AS date_week;
SELECT DATE_TRUNC('day', CAST('2024-05-16' AS DATE)) AS date_day;

CREATE TABLE readings (d DATETIME, value INT);
INSERT INTO readings VALUES ('2024-05-16 12:01:00', 1), ('2024-05-16 12:45:00', 2), ('2024-05-16 13:01:00', 3);
SELECT DATE_TRUNC('hour', d) AS bucket, SUM(value) AS total FROM readings GROUP BY bucket ORDER BY bucket;

DROP DATABASE date_trunc_demo;

The following unit is not valid for a DATE value:

-- Expected-Success: false
SELECT DATE_TRUNC('hour', CAST('2024-05-16' AS DATE));