date_ceil

description

Syntax

DATETIME DATE_CEIL(DATETIME datetime, INTERVAL period type)

将日期转化为指定的时间间隔周期的最近上取整时刻。

datetime 参数是合法的日期表达式。

period 参数是指定每个周期有多少个单位组成,开始的时间起点为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)
  8. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 minute);
  9. +--------------------------------------------------------------+
  10. | minute_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  11. +--------------------------------------------------------------+
  12. | 2023-07-13 22:30:00 |
  13. +--------------------------------------------------------------+
  14. 1 row in set (0.01 sec)
  15. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 hour);
  16. +------------------------------------------------------------+
  17. | hour_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  18. +------------------------------------------------------------+
  19. | 2023-07-13 23:00:00 |
  20. +------------------------------------------------------------+
  21. 1 row in set (0.01 sec)
  22. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 day);
  23. +-----------------------------------------------------------+
  24. | day_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  25. +-----------------------------------------------------------+
  26. | 2023-07-15 00:00:00 |
  27. +-----------------------------------------------------------+
  28. 1 row in set (0.00 sec)
  29. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 month);
  30. +-------------------------------------------------------------+
  31. | month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  32. +-------------------------------------------------------------+
  33. | 2023-12-01 00:00:00 |
  34. +-------------------------------------------------------------+
  35. 1 row in set (0.01 sec)
  36. mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 year);
  37. +------------------------------------------------------------+
  38. | year_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  39. +------------------------------------------------------------+
  40. | 2026-01-01 00:00:00 |
  41. +------------------------------------------------------------+
  42. 1 row in set (0.00 sec)

keywords

  1. DATE_CEIL,DATE,CEIL