DATE_FLOOR

Description

date_floor rounds a given date to the closest lower boundary of the specified time interval.

Syntax

DATE_FLOOR(<datetime>, INTERVAL <period> <type>)

Parameters

Parameter	Description
datetime	The argument is a valid date expression
period	The argument specifies how many units make up each period, with the start time being 0001-01-01T00:00:00
type	The argument can be: YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND

Return Value

The return value is a date or time, representing the result of rounding the input value down to the specified unit.

Examples

select date_floor("0001-01-01 00:00:16",interval 5 second);

+---------------------------------------------------------------+
| second_floor('0001-01-01 00:00:16', 5, '0001-01-01 00:00:00') |
+---------------------------------------------------------------+
| 0001-01-01 00:00:15                                           |
+---------------------------------------------------------------+

select date_floor("0001-01-01 00:00:18",interval 5 second);

+---------------------------------------------------------------+
| second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
+---------------------------------------------------------------+
| 0001-01-01 00:00:15                                           |
+---------------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 minute);

+---------------------------------------------------------------+
| minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+---------------------------------------------------------------+
| 2023-07-13 22:25:00                                           |
+---------------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 hour);

+-------------------------------------------------------------+
| hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+-------------------------------------------------------------+
| 2023-07-13 18:00:00                                         |
+-------------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 day);

+------------------------------------------------------------+
| day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+------------------------------------------------------------+
| 2023-07-10 00:00:00                                        |
+------------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 month);

+--------------------------------------------------------------+
| month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+--------------------------------------------------------------+
| 2023-07-01 00:00:00                                          |
+--------------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 quarter);

+------------------------------------------------------+
| date_floor("2023-07-13 22:28:18",interval 5 quarter) |
+------------------------------------------------------+
| 2023-07-01 00:00:00                                  |
+------------------------------------------------------+

select date_floor("2023-07-13 22:28:18",interval 5 year);

+-------------------------------------------------------------+
| year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
+-------------------------------------------------------------+
| 2021-01-01 00:00:00                                         |
+-------------------------------------------------------------+