date_ceil

description

Syntax

DATETIME DATE_CEIL(DATETIME datetime, INTERVAL period type)

Convert the date to the nearest rounding up time of the specified time interval period.

The datetime parameter is a valid date expression.

The period parameter specifies how many units each cycle consists of, starting from 0001-01-01T00:00:00

type ：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

example

1. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 second);
2. +--------------------------------------------------------------+
3. | second_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
4. +--------------------------------------------------------------+
5. | 2023-07-13 22:28:20                                          |
6. +--------------------------------------------------------------+
7. 1 row in set (0.01 sec)

9. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 minute);
10. +--------------------------------------------------------------+
11. | minute_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
12. +--------------------------------------------------------------+
13. | 2023-07-13 22:30:00                                          |
14. +--------------------------------------------------------------+
15. 1 row in set (0.01 sec)

17. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 hour);
18. +------------------------------------------------------------+
19. | hour_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
20. +------------------------------------------------------------+
21. | 2023-07-13 23:00:00                                        |
22. +------------------------------------------------------------+
23. 1 row in set (0.01 sec)

25. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 day);
26. +-----------------------------------------------------------+
27. | day_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
28. +-----------------------------------------------------------+
29. | 2023-07-15 00:00:00                                       |
30. +-----------------------------------------------------------+
31. 1 row in set (0.00 sec)

33. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 month);
34. +-------------------------------------------------------------+
35. | month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
36. +-------------------------------------------------------------+
37. | 2023-12-01 00:00:00                                         |
38. +-------------------------------------------------------------+
39. 1 row in set (0.01 sec)

41. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 year);
42. +------------------------------------------------------------+
43. | year_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
44. +------------------------------------------------------------+
45. | 2026-01-01 00:00:00                                        |
46. +------------------------------------------------------------+
47. 1 row in set (0.00 sec)

keywords

1. DATE_CEIL,DATE,CEIL