SingleStore DB

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 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 orders
GROUP BY 1;

Which results in:

+---------------------------------+-------------------+
| 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 orders
GROUP BY 1
ORDER BY 1;

Which results in:

+--------------------------------+-------------------+
| 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 orders
GROUP BY 1
ORDER BY 1;

Which results in:

+------------+-----------------------+
| order_week | sum_order_week_amount |
+------------+-----------------------+
| 2017-01-02 |              30144.43 |
| 2017-01-09 |              20693.14 |
| 2017-01-16 |               6700.55 |
+------------+-----------------------+