DATE_TRUNC

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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

Last modified: May 31, 2023