SingleStoreDB v8.1

DATE_TRUNC

Truncates a timestamp using the specified date part.

Returns a truncated timestamp where the date part is used as the level of precision.

Syntax

DATE_TRUNC('datepart', timestamp)

Arguments

'datepart': The date part used to truncate the timestamp, which can be one of the following levels of precision:
- year
- quarter
- month
- week
- day
- hour
- minute
- second
- microseconds
timestamp: The timestamp to truncate, either as a column of TIMESTAMP type or an expression that evaluates to TIMESTAMP.

Note

When datepart is set to week, the timestamp specified is truncated to start on the first day of the week, which is Monday in SingleStore.

Return Type

TIMESTAMP

Example 1: Hours and Minutes

Truncate a timestamp to the hour date part:

SELECT DATE_TRUNC('hour', '2016-08-08 12:05:31');
****
+-------------------------------------------+
| DATE_TRUNC('hour', '2016-08-08 12:05:31') |
+-------------------------------------------+
| 2016-08-08 12:00:00                       |
+-------------------------------------------+

Truncate a timestamp to the minute date part:

SELECT DATE_TRUNC('minute', '2016-08-08 12:05:31');
****
+---------------------------------------------+
| DATE_TRUNC('minute', '2016-08-08 12:05:31') |
+---------------------------------------------+
| 2016-08-08 12:05:00                         |
+---------------------------------------------+
1 row in set (0.07 sec)

Example 2: Months and Weeks

Consider the following example table named dt_orders:

SELECT * FROM dt_orders;
****
+----+-------------+--------------+---------------------+
| id | customer_id | order_amount | order_time          |
+----+-------------+--------------+---------------------+
|  5 |      677222 |     19973.03 | 2017-01-12 00:00:00 |
|  2 |      656590 |     13666.29 | 2017-01-05 00:00:00 |
|  4 |      941937 |       720.11 | 2017-01-13 00:00:00 |
|  1 |      656590 |      6700.55 | 2017-01-18 00:00:00 |
|  3 |      941937 |     16478.14 | 2017-01-06 00:00:00 |
+----+-------------+--------------+---------------------+
5 rows in set (0.10 sec)

To get the sum of order_amount for the month of January 2017:

SELECT DATE_TRUNC('month', order_time), SUM(order_amount)
FROM dt_orders
GROUP BY 1;
****
+---------------------------------+-------------------+
| DATE_TRUNC('month', order_time) | SUM(order_amount) |
+---------------------------------+-------------------+
| 2017-01-01 00:00:00.000000      |          57538.12 |
+---------------------------------+-------------------+

To get the order_amount for each week in January 2017:

SELECT DATE_TRUNC('week', order_time), SUM(order_amount)
FROM dt_orders
GROUP BY 1
ORDER BY 1;
****
+--------------------------------+-------------------+
| DATE_TRUNC('week', order_time) | SUM(order_amount) |
+--------------------------------+-------------------+
| 2017-01-02 00:00:00.000000     |          30144.43 |
| 2017-01-09 00:00:00.000000     |          20693.14 |
| 2017-01-16 00:00:00.000000     |           6700.55 |
+--------------------------------+-------------------+

To get the order_amount for each week in January 2017 with more descriptive column names:

SELECT CAST(DATE_TRUNC('week', order_time) AS DATE) AS order_week, SUM(order_amount) AS sum_order_week_amount
FROM dt_orders
GROUP BY 1
ORDER BY 1;
****
+------------+-----------------------+
| order_week | sum_order_week_amount |
+------------+-----------------------+
| 2017-01-02 |              30144.43 |
| 2017-01-09 |              20693.14 |
| 2017-01-16 |               6700.55 |
+------------+-----------------------+

Remarks

The DATE_TRUNC function supports improved segment elimination when used in WHERE clauses. See the following query example:

SELECT ... FROM table_name WHERE DATE_TRUNC('year', column_name) = '2022-01-01 00:00:00';

In this section: