date_floor

description

Syntax

DATETIME DATE_FLOOR(DATETIME datetime, INTERVAL period type)

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

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

period 参数是指定每个周期有多少个单位组成,开始的时间起点为0001-01-01T00:00:00.

type 参数可以是下列值:YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

example

  1. mysql>select date_floor("0001-01-01 00:00:16",interval 5 second);
  2. +---------------------------------------------------------------+
  3. | second_floor('0001-01-01 00:00:16', 5, '0001-01-01 00:00:00') |
  4. +---------------------------------------------------------------+
  5. | 0001-01-01 00:00:15 |
  6. +---------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8. mysql>select date_floor("0001-01-01 00:00:18",interval 5 second);
  9. +---------------------------------------------------------------+
  10. | second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
  11. +---------------------------------------------------------------+
  12. | 0001-01-01 00:00:15 |
  13. +---------------------------------------------------------------+
  14. 1 row in set (0.01 sec)
  15. mysql>select date_floor("2023-07-13 22:28:18",interval 5 minute);
  16. +---------------------------------------------------------------+
  17. | minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  18. +---------------------------------------------------------------+
  19. | 2023-07-13 22:25:00 |
  20. +---------------------------------------------------------------+
  21. 1 row in set (0.00 sec)
  22. mysql>select date_floor("2023-07-13 22:28:18",interval 5 hour);
  23. +-------------------------------------------------------------+
  24. | hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  25. +-------------------------------------------------------------+
  26. | 2023-07-13 18:00:00 |
  27. +-------------------------------------------------------------+
  28. 1 row in set (0.01 sec)
  29. mysql>select date_floor("2023-07-13 22:28:18",interval 5 day);
  30. +------------------------------------------------------------+
  31. | day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  32. +------------------------------------------------------------+
  33. | 2023-07-10 00:00:00 |
  34. +------------------------------------------------------------+
  35. 1 row in set (0.00 sec)
  36. mysql>select date_floor("2023-07-13 22:28:18",interval 5 month);
  37. +--------------------------------------------------------------+
  38. | month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  39. +--------------------------------------------------------------+
  40. | 2023-07-01 00:00:00 |
  41. +--------------------------------------------------------------+
  42. 1 row in set (0.01 sec)
  43. mysql>select date_floor("2023-07-13 22:28:18",interval 5 year);
  44. +-------------------------------------------------------------+
  45. | year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  46. +-------------------------------------------------------------+
  47. | 2021-01-01 00:00:00 |
  48. +-------------------------------------------------------------+

keywords

  1. DATE_FLOOR,DATE,FLOOR