date_trunc
date_trunc
description
Syntax
DATETIME DATE_TRUNC(DATETIME datetime, VARCHAR unit)
将datetime按照指定的时间单位截断。
datetime 参数是合法的日期表达式。
unit 参数是您希望截断的时间间隔，可选的值如下：[second,minute,hour,day,week,month,quarter,year]。
example
mysql> select date_trunc('2010-12-02 19:28:30', 'second');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'second') |
+-------------------------------------------------+
| 2010-12-02 19:28:30 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'minute');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'minute') |
+-------------------------------------------------+
| 2010-12-02 19:28:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'hour');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'hour') |
+-------------------------------------------------+
| 2010-12-02 19:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'day');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'day') |
+-------------------------------------------------+
| 2010-12-02 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2023-4-05 19:28:30', 'week');
+-------------------------------------------+
| date_trunc('2023-04-05 19:28:30', 'week') |
+-------------------------------------------+
| 2023-04-03 00:00:00 |
+-------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'month');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'month') |
+-------------------------------------------------+
| 2010-12-01 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'quarter');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'quarter') |
+-------------------------------------------------+
| 2010-10-01 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'year');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'year') |
+-------------------------------------------------+
| 2010-01-01 00:00:00 |
+-------------------------------------------------+keywords
DATE_TRUNC,DATE,TRUNC