time_round
time_round
description
Syntax
DATETIME TIME_ROUND(DATETIME expr)
DATETIME TIME_ROUND(DATETIME expr, INT period)
DATETIME TIME_ROUND(DATETIME expr, DATETIME origin)
DATETIME TIME_ROUND(DATETIME expr, INT period, DATETIME origin)
函数名 TIME_ROUND
由两部分组成,每部分由以下可选值组成
TIME
:SECOND
,MINUTE
,HOUR
,DAY
,WEEK
,MONTH
,YEAR
ROUND
:FLOOR
,CEIL
返回 expr
的上/下界。
period
指定每个周期有多少个TIME
单位组成,默认为1
。origin
指定周期的开始时间,默认为1970-01-01T00:00:00
,WEEK
的默认开始时间为1970-01-04T00:00:00
,即周日。可以比expr
大。- 请尽量选择常见
period
,如 3MONTH
,90MINUTE
等,如设置了非常用period
,请同时指定origin
。
example
MySQL> SELECT YEAR_FLOOR('20200202000000');
+------------------------------+
| year_floor('20200202000000') |
+------------------------------+
| 2020-01-01 00:00:00 |
+------------------------------+
MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3); --quarter
+--------------------------------------------------------+
| month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3) |
+--------------------------------------------------------+
| 2020-04-01 00:00:00 |
+--------------------------------------------------------+
MySQL> SELECT WEEK_CEIL('2020-02-02 13:09:20', '2020-01-06'); --monday
+---------------------------------------------------------+
| week_ceil('2020-02-02 13:09:20', '2020-01-06 00:00:00') |
+---------------------------------------------------------+
| 2020-02-03 00:00:00 |
+---------------------------------------------------------+
MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)); --next rent day
+-------------------------------------------------------------------------------------------------+
| month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)) |
+-------------------------------------------------------------------------------------------------+
| 2020-04-09 00:00:00 |
+-------------------------------------------------------------------------------------------------+
keywords
TIME_ROUND