DATE_CEIL

描述

DATE_CEIL 函数用于将指定的日期或时间值向上取整（ceil）到最近的指定时间间隔周期的起点。即返回不小于输入日期时间的最小周期时刻，周期规则由 period（周期数量）和 type（周期单位）共同定义，且所有周期均以固定起点 0001-01-01 00:00:00 为基准计算。

日期计算公式：

$$\begin{aligned} &\text{date\_ceil}(\langle\text{date\_or\_time\_expr}\rangle, \langle\text{period}\rangle, \langle\text{type}\rangle) = \\ &\min\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{type} \mid \\ &k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{type} \geq \langle\text{date\_or\_time\_expr}\rangle\} \end{aligned}$$

$k$ 代表基准时间到达目标时间所需的周期数

$type$ 代表的是周期单位

语法

DATE_CEIL(<date_or_time_expr>, <period> <type>)

参数

参数	说明
<date_or_time_expr>	参数是合法的日期表达式，支持输入 date/datetime/timestamptz 类型，具体格式请查看 timestamptz的转换, datetime 的转换 和 date 的转换
<period>	参数是指定每个周期有多少个单位组成，类型为 INT, 开始的时间起点为 0001-01-01T00:00:00
<type>	参数可以是：YEAR, QUARTER, MONTH, WEEK ,DAY, HOUR, MINUTE, SECOND

返回值

返回类型为 TIMESTAMPTZ, DATETIME 或 DATE。返回的是一个日期或时间值，表示将输入值向上舍入到指定单位的结果。

返回与 <date_or_time_expr> 类型一致的取整结果：

• 若输入为 TIMESTAMPTZ 类型，则会先将其转换为 local_time(如：2025-12-31 23:59:59+05:00 在会话变量为+08:00的情况下代表的local_time为2026-01-01 02:59:59),再进行 DATE_CEIL 计算操作。
• 输入 DATE 类型时，返回 DATE（仅日期部分）；
• 输入 DATETIME 类型，返回 DATETIME（包含日期和时间）。
• 输出 TIMESTAMPTZ 类型，返回 TIMESTAMPTZ（包含日期、时间和偏移量）。
• 对于带有 scale 的 DATETIME 和 TIMESTAMPTZ，返回值也会带有 scale，小数部分为零。

特殊情况：

• 任何参数为 NULL 时，返回 NULL；
• 若取整结果超出日期类型支持的范围（如 '9999-12-31 23:59:59' 之后），返回错误。
• 若 period 参数为非正数，抛出错误

举例

-- 秒数按五秒向上取整
mysql> select date_ceil(cast("2023-07-13 22:28:18" as datetime),interval 5 second);
+------------------------------------------------------------------------+
| date_ceil(cast("2023-07-13 22:28:18" as datetime),interval 5 second) |
+------------------------------------------------------------------------+
| 2023-07-13 22:28:20.000000                                             |
+------------------------------------------------------------------------+

-- 带有 scale 的日期时间参数
mysql> select date_ceil(cast("2023-07-13 22:28:18.123" as datetime(3)),interval 5 second);
+-----------------------------------------------------------------------------+
| date_ceil(cast("2023-07-13 22:28:18.123" as datetime(3)),interval 5 second) |
+-----------------------------------------------------------------------------+
| 2023-07-13 22:28:20.000                                                     |
+-----------------------------------------------------------------------------+

-- 按五分钟向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 minute);
+--------------------------------------------------------------+
| minute_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+--------------------------------------------------------------+
| 2023-07-13 22:30:00                                          |
+--------------------------------------------------------------+

-- 按五周向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 WEEK);
+--------------------------------------------------+
| date_ceil("2023-07-13 22:28:18",interval 5 WEEK) |
+--------------------------------------------------+
| 2023-08-14 00:00:00                              |
+--------------------------------------------------+

-- 按五小时向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 hour);
+--------------------------------------------------+
| date_ceil("2023-07-13 22:28:18",interval 5 hour) |
+--------------------------------------------------+
| 2023-07-13 23:00:00                   |
+--------------------------------------------------+

-- 按五天向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 day);
+-----------------------------------------------------------+
| day_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+-----------------------------------------------------------+
| 2023-07-15 00:00:00                                       |
+-----------------------------------------------------------+

-- 按五个月向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 month);
+-------------------------------------------------------------+
| month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+-------------------------------------------------------------+
| 2023-12-01 00:00:00                                         |
+-------------------------------------------------------------+

--按五年向上取整
select date_ceil("2023-07-13 22:28:18",interval 5 year);
+-------------------------------------------------------------+
| month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+-------------------------------------------------------------+
| 2023-12-01 00:00:00                                         |
+-------------------------------------------------------------+

-- TimeStampTz类型样例, SET time_zone = '+08:00'
-- 将变量值转换为 local_time(2026-01-01 02:59:59)后再做 DATE_CEIL 操作
SELECT DATE_CEIL('2025-12-31 23:59:59+05:00', INTERVAL 1 YEAR);
+---------------------------------------------------------+
| DATE_CEIL('2025-12-31 23:59:59+05:00', INTERVAL 1 YEAR) |
+---------------------------------------------------------+
| 2027-01-01 00:00:00+08:00                               |
+---------------------------------------------------------+

-- 超过最大年数
mysql> select date_ceil("9999-07-13",interval 5 year);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation year_ceil of 9999-07-13 00:00:00, 5 out of range

--任一参数为 NULL
mysql> select date_ceil("9900-07-13",interval NULL year);
+--------------------------------------------+
| date_ceil("9900-07-13",interval NULL year) |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+

mysql> select date_ceil(NULL,interval 5 year);
+---------------------------------+
| date_ceil(NULL,interval 5 year) |
+---------------------------------+
| NULL                            |
+---------------------------------+

-- 无效参数，period 为负数
mysql> select date_ceil("2023-01-13 22:28:18",interval -5 month);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[INVALID_ARGUMENT]Operation month_ceil of 2023-01-13 22:28:18, -5, 0001-01-01 00:00:00 out of range