DATE_TRUNC
描述
将 datetime 按照指定的时间单位截断。
语法
DATE_TRUNC(<datetime>, <time_unit>)
DATE_TRUNC(<time_unit>, <datetime>)
从 Doris 3.0.5 起，DATE_TRUNC 支持以上两种参数顺序；Doris 3.1 也支持两种顺序。在 Doris 3.0.5 之前的 3.0 版本中，请使用 DATE_TRUNC(<datetime>, <time_unit>)，将时间单位放在第二个参数。
参数
| 参数 | 说明 |
|---|---|
<datetime> | 合法的日期表达式 |
<time_unit> | 希望截断的时间间隔，可选的值如下：[second,minute,hour,day,week,month,quarter,year] |
返回值
按照指定的时间单位截断的时间
举例
以下两种写法均适用于 Doris 3.0.5 及更高的 3.x 版本，结果相同：
SELECT DATE_TRUNC('2025-04-28 12:12:12', 'year') AS truncated;
+---------------------+
| truncated |
+---------------------+
| 2025-01-01 00:00:00 |
+---------------------+
SELECT DATE_TRUNC('year', '2025-04-28 12:12:12') AS truncated;
+---------------------+
| truncated |
+---------------------+
| 2025-01-01 00:00:00 |
+---------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------------+
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 |
+-------------------------------------------------+