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.

Return Type

TIMESTAMP

Example 1: Hours and Minutes

Truncate a timestamp to the hour date part:

memsql> 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                       |
+-------------------------------------------+
1 row in set (0.08 sec)

Truncate a timestamp to the minute date part:

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

Was this Article Helpful?

Thanks For Your Feedback!