Link Search Menu Expand Document

DATE_TRUNC

Truncate a given date to a specified position.

Syntax

DATE_TRUNC('<precision>', <date>)
Parameter Description
<precision> The time unit for the returned value to be expressed. This can be any of the following: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, EPOCH
<date> The date to be truncated. This can be any expression that evaluates to a DATE or TIMESTAMP value.

Example

The example below uses a table date_test with the columns and values below.

Category sale_datetime
a 2017-06-15 09:34:21
b 2014-01-15 12:14:46
c 1999-09-15 11:33:21
SELECT
	category,
	sale_datetime,
	DATE_TRUNC('MINUTE', sale_datetime) AS MINUTE,
	DATE_TRUNC('HOUR', sale_datetime) AS HOUR,
	DATE_TRUNC('DAY', sale_datetime) AS DAY
FROM
	date_test
ORDER BY
	category;

Returns:

+----------+---------------------+---------------------+---------------------+---------------------+
| category | sale_datetime       | MINUTE              | HOUR                | DAY                 |
| a        | 2017-06-15 09:34:21 | 2017-06-15 09:34:00 | 2017-06-15 09:00:00 | 2017-06-15 00:00:00 |
| b        | 2014-01-15 12:14:46 | 2014-01-15 12:14:00 | 2014-01-15 12:00:00 | 2014-01-15 00:00:00 |
| c        | 1999-09-15 11:33:21 | 1999-09-15 11:33:00 | 1999-09-15 11:00:00 | 1999-09-15 00:00:00 |
+----------+---------------------+---------------------+---------------------+---------------------+