DATE_FLOOR

Description

The date_floor function floors a specified date or time value down to the nearest start of a specified time interval period. It supports DATE, DATETIME, TIMESTAMP_NS, and TIMESTAMPTZ inputs. The period rules are defined by period (number of units) and type (unit), calculated from the fixed starting point 0001-01-01 00:00:00.

Date calculation formula:

$$\begin{aligned} &\text{date\_floor}(\langle\text{date\_or\_time\_expr}\rangle, \langle\text{period}\rangle, \langle\text{type}\rangle) = \\ &\max\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{type} \mid \\ &k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{type} \leq \langle\text{date\_or\_time\_expr}\rangle\} \end{aligned}$$

$k$ represents the number of cycles required for the base time to reach the target time.

$type$ represents the unit of period

Syntax

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

Parameters

Parameter	Description
date_or_time_expr	A valid date expression. Supports DATE, DATETIME, TIMESTAMP_NS, and TIMESTAMPTZ. For specific formats, please refer to timestamptz conversion, datetime conversion and date conversion.
period	Specifies the number of units each period consists of, of INT type. The starting time point is 0001-01-01T00:00:00
type	Can be: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Return Value

A TIMESTAMP_NS input returns TIMESTAMP_NS with fixed nine-digit fractional-second precision. Results are validated against the range of the return type; TIMESTAMP_NS uses its range of [1677-09-21 00:12:43.145224192, 2262-04-11 23:47:16.854775807].

When <origin> is omitted for a TIMESTAMP_NS input, the documented default origin is used only as an internal alignment reference; it does not need to fall within the storable TIMESTAMP_NS range.

The return type is TIMESTAMPTZ, TIMESTAMP_NS, DATETIME, or DATE. Returns the result of flooring the date down according to the period, with the same type as <date_or_time_expr>.

Returns a floored result consistent with the <date_or_time_expr> type:

• If the input is TIMESTAMPTZ type, it is first converted to local_time (e.g., 2025-12-31 23:59:59+05:00 represents local_time 2026-01-01 02:59:59 when the session variable is +08:00), and then the DATE_FLOOR calculation is performed.
• When input is DATE type, returns DATE (only the date part);
• When input is DATETIME type, returns DATETIME (including date and time).
• When input is TIMESTAMP_NS, returns TIMESTAMP_NS with nine fractional-second digits.
• When input is TIMESTAMPTZ type, returns TIMESTAMPTZ (including date, time, and offset).
• Input with scale will return value with scale, decimal part equal zero.

Special cases:

• Returns NULL if any parameter is NULL;
• Returns an error for illegal period (non-positive integers) or type;

Examples

-- Floor down to the nearest 5-second interval (period starts at 00, 05, 10... seconds)
mysql> select date_floor(cast("0001-01-01 00:00:18" as datetime), INTERVAL 5 SECOND);
+------------------------------------------------------------------------+
| date_floor(cast("0001-01-01 00:00:18" as datetime), INTERVAL 5 SECOND) |
+------------------------------------------------------------------------+
| 0001-01-01 00:00:15.000000                                             |
+------------------------------------------------------------------------+

-- Date time with scale will return value with scale
mysql> select date_floor(cast("0001-01-01 00:00:18.123" as datetime), INTERVAL 5 SECOND);
+----------------------------------------------------------------------------+
| date_floor(cast("0001-01-01 00:00:18.123" as datetime), INTERVAL 5 SECOND) |
+----------------------------------------------------------------------------+
| 0001-01-01 00:00:15.000000                                                 |
+----------------------------------------------------------------------------+

-- The input time is exactly the start of a 5-day period
mysql> select date_floor("2023-07-10 00:00:00", INTERVAL 5 DAY);
+---------------------------------------------------+
| date_floor("2023-07-10 00:00:00", INTERVAL 5 DAY) |
+---------------------------------------------------+
| 2023-07-10 00:00:00                               |
+---------------------------------------------------+

-- Floor down for date type
mysql> select date_floor("2023-07-13", INTERVAL 5 YEAR);
+-------------------------------------------+
| date_floor("2023-07-13", INTERVAL 5 YEAR) |
+-------------------------------------------+
| 2021-01-01 00:00:00                       |
+-------------------------------------------+

-- TimeStampTz type example, SET time_zone = '+08:00'
-- Convert variable value to local_time(2026-01-01 02:59:59) then perform DATE_FLOOR operation
SELECT DATE_FLOOR('2025-12-31 23:59:59+05:00', INTERVAL 1 YEAR);
+----------------------------------------------------------+
| DATE_FLOOR('2025-12-31 23:59:59+05:00', INTERVAL 1 YEAR) |
+----------------------------------------------------------+
| 2026-01-01 00:00:00+08:00                                |
+----------------------------------------------------------+

-- If parameters contain both TimeStampTz and Datetime types, output DateTime type
SELECT DATE_FLOOR('2025-12-31 23:59:59+05:00', INTERVAL 1 HOUR, '2025-12-15 00:00:00.123') AS result;

-- period is negative, invalid returns error
mysql> select date_floor("2023-07-13 22:28:18", INTERVAL -5 MINUTE);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation minute_floor of 2023-07-13 22:28:18, -5, 0001-01-01 00:00:00 out of range

-- Unsupported type
mysql> select date_floor("2023-07-13 22:28:18", INTERVAL 5 MILLISECOND);
ERROR 1105 (HY000): errCode = 2, detailMessage = 
mismatched input 'MILLISECOND' expecting {'.', '[', 'AND', 'BETWEEN', 'COLLATE', 'DAY', 'DIV', 'HOUR', 'IN', 'IS', 'LIKE', 'MATCH', 'MATCH_ALL', 'MATCH_ANY', 'MATCH_PHRASE', 'MATCH_PHRASE_EDGE', 'MATCH_PHRASE_PREFIX', 'MATCH_REGEXP', 'MINUTE', 'MONTH', 'NOT', 'OR', 'QUARTER', 'REGEXP', 'RLIKE', 'SECOND', 'WEEK', 'XOR', 'YEAR', EQ, '<=>', NEQ, '<', LTE, '>', GTE, '+', '-', '*', '/', '%', '&', '&&', '|', '||', '^'}(line 1, pos 52)

-- Any parameter is NULL
mysql> select date_floor(NULL, INTERVAL 5 HOUR);
+-----------------------------------+
| date_floor(NULL, INTERVAL 5 HOUR) |
+-----------------------------------+
| NULL                              |
+-----------------------------------+

-- Floor down every 5 weeks
mysql> select date_floor("2023-07-13 22:28:18", INTERVAL 5 WEEK);
+----------------------------------------------------+
| date_floor("2023-07-13 22:28:18", INTERVAL 5 WEEK) |
+----------------------------------------------------+
| 2023-07-10 00:00:00                                |
+----------------------------------------------------+