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 thetimestamp
, 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 ofTIMESTAMP
type or an expression that evaluates toTIMESTAMP
.
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';