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