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