跳到主要内容

DATE_TRUNC

描述

DATE_TRUNC 函数用于将日期或时间值(datetime)按照指定的时间单位(time_unit)截断,即保留指定单位及更高层级的时间信息,将更低层级的时间信息清至最小日期时间。例如,按 “小时” 截断时,会保留年、月、日、小时,将分钟、秒等清零,按照年截断时,会把日,月截断为 xxxx-01-01。

该函数与 postgresql 中的 date_trunc函数 行为基本一致, 不同的是, doris暂不支持 second 单位以下的截断, postgresql 支持到 microsecond 。

语法

DATE_TRUNC(<datetime>, <time_unit>)
DATE_TRUNC(<time_unit>, <datetime>)

参数

参数说明
<date_or_time_part>合法的日期表达式, 类型为 datetime 或者 date 类型, 具体 datetime 和 date 格式请查看 datetime 的转换date 的转换)
<time_unit>希望截断的时间间隔,可选的值如下:[second,minute,hour,day,week,month,quarter,year]

返回值

返回与 datetime 类型一致的截断结果:

  • 输入 DATE 时,返回 DATE 类型;
  • 输入 DATETIME 或带时间的字符串时,返回 DATETIME(包含日期和截断后的时间)。
  • 对于带有 scale 的 datetime 类型,会截小数为零但保留 scale 返回.

特殊情况:

  • 任何参数为 NULL 时,返回 NULL;
  • 不支持的 time_unit 时,返回错误。

举例


--- 按照秒,分,时,日,周,月,季度,年 来截断
mysql> select date_trunc(cast('2010-12-02 19:28:30' as datetime), 'second');
+---------------------------------------------------------------+
| date_trunc(cast('2010-12-02 19:28:30' as datetime), '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(cast('2010-12-02' as date), 'month');
+-------------------------------------------------+
| date_trunc(cast('2010-12-02' as date), 'month') |
+-------------------------------------------------+
| 2010-12-01 |
+-------------------------------------------------+

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

---对于带有 scale 的日期时间,会截断小数位为零不进行四舍五入,但返回值带有 scale
mysql> select date_trunc('2010-12-02 19:28:30.523', 'second');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30.523', 'second') |
+-------------------------------------------------+
| 2010-12-02 19:28:30.000 |
+-------------------------------------------------+

---不支持的单位,返回错误
select date_trunc('2010-12-02 19:28:30', 'quar');
ERROR 1105 (HY000): errCode = 2, detailMessage = date_trunc function time unit param only support argument is year|quarter|month|week|day|hour|minute|second