DATE_FLOOR
更新时间：2025-10-17
描述
date_floor 将日期转化为指定的时间间隔周期的最近下取整时刻。
语法
DATE_FLOOR(<datetime>, INTERVAL <period> <type>)
参数
| 参数 | 说明 |
|---|---|
datetime |
参数是合法的日期表达式 |
period |
参数是指定每个周期有多少个单位组成，开始的时间起点为 0001-01-01T00:00:00 |
type |
参数可以是：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND |
返回值
返回的是一个日期或时间值，表示将输入值向下舍入到指定单位的结果。
举例
SQL
1select date_floor("0001-01-01 00:00:16",interval 5 second);
Text
1+---------------------------------------------------------------+
2| second_floor('0001-01-01 00:00:16', 5, '0001-01-01 00:00:00') |
3+---------------------------------------------------------------+
4| 0001-01-01 00:00:15 |
5+---------------------------------------------------------------+
SQL
1select date_floor("0001-01-01 00:00:18",interval 5 second);
Text
1+---------------------------------------------------------------+
2| second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
3+---------------------------------------------------------------+
4| 0001-01-01 00:00:15 |
5+---------------------------------------------------------------+
SQL
1select date_floor("2023-07-13 22:28:18",interval 5 minute);
Text
1+---------------------------------------------------------------+
2| minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
3+---------------------------------------------------------------+
4| 2023-07-13 22:25:00 |
5+---------------------------------------------------------------+
SQL
1select date_floor("2023-07-13 22:28:18",interval 5 hour);
Text
1+-------------------------------------------------------------+
2| hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
3+-------------------------------------------------------------+
4| 2023-07-13 18:00:00 |
5+-------------------------------------------------------------+
SQL
1select date_floor("2023-07-13 22:28:18",interval 5 day);
Text
1+------------------------------------------------------------+
2| day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
3+------------------------------------------------------------+
4| 2023-07-10 00:00:00 |
5+------------------------------------------------------------+
SQL
1select date_floor("2023-07-13 22:28:18",interval 5 month);
Text
1+--------------------------------------------------------------+
2| month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
3+--------------------------------------------------------------+
4| 2023-07-01 00:00:00 |
5+--------------------------------------------------------------+
SQL
1select date_floor("2023-07-13 22:28:18",interval 5 year);
Text
1+-------------------------------------------------------------+
2| year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
3+-------------------------------------------------------------+
4| 2021-01-01 00:00:00 |
5+-------------------------------------------------------------+