date_floor

Description

Syntax

DATETIME DATE_FLOOR(DATETIME datetime, INTERVAL period type)

Converts a date to the nearest rounding down time of a 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>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)
  9. mysql>select date_floor("0001-01-01 00:00:18",interval 5 second);
  10. +---------------------------------------------------------------+
  11. | second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
  12. +---------------------------------------------------------------+
  13. | 0001-01-01 00:00:15                                           |
  14. +---------------------------------------------------------------+
  15. 1 row in set (0.01 sec)
  17. mysql>select date_floor("2023-07-13 22:28:18",interval 5 minute);
  18. +---------------------------------------------------------------+
  19. | minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  20. +---------------------------------------------------------------+
  21. | 2023-07-13 22:25:00                                           |
  22. +---------------------------------------------------------------+
  23. 1 row in set (0.00 sec)
  25. mysql>select date_floor("2023-07-13 22:28:18",interval 5 hour);
  26. +-------------------------------------------------------------+
  27. | hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  28. +-------------------------------------------------------------+
  29. | 2023-07-13 18:00:00                                         |
  30. +-------------------------------------------------------------+
  31. 1 row in set (0.01 sec)
  33. mysql>select date_floor("2023-07-13 22:28:18",interval 5 day);
  34. +------------------------------------------------------------+
  35. | day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  36. +------------------------------------------------------------+
  37. | 2023-07-10 00:00:00                                        |
  38. +------------------------------------------------------------+
  39. 1 row in set (0.00 sec)
  41. mysql>select date_floor("2023-07-13 22:28:18",interval 5 month);
  42. +--------------------------------------------------------------+
  43. | month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  44. +--------------------------------------------------------------+
  45. | 2023-07-01 00:00:00                                          |
  46. +--------------------------------------------------------------+
  47. 1 row in set (0.01 sec)
  49. mysql>select date_floor("2023-07-13 22:28:18",interval 5 year);
  50. +-------------------------------------------------------------+
  51. | year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
  52. +-------------------------------------------------------------+
  53. | 2021-01-01 00:00:00                                         |
  54. +-------------------------------------------------------------+

Keywords

  1. DATE_FLOOR,DATE,FLOOR

Best Practices

See also