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 |
+----------+---------------------+---------------------+---------------------+---------------------+