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

Best Practices

See also