MINUTES_SUB

Description

The MINUTES_SUB function subtracts a specified number of minutes from the input datetime value and returns the resulting new datetime value. This function supports processing DATE, DATETIME, TIMESTAMP_NS, and TIMESTAMPTZ types.

This function is consistent with date_sub function and MySQL's date_sub function when using MINUTE as the unit.

Syntax

MINUTES_SUB(`<date_or_time_expr>`, `<minutes>`)

Parameters

Parameter	Description
<date_or_time_expr>	The input datetime value, which can be of type DATE, DATETIME, TIMESTAMP_NS or TIMESTAMPTZ. For specific datetime/date formats, see timestamptz conversion, datetime conversion and date conversion.
<minutes>	The number of minutes to subtract, of type BIGINT. Can be positive or negative.

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].

Return the result of subtracting the specified minutes <minutes> from the base time <datetime_like_type>, with the return type being related to the type of the first parameter:

• If the first parameter is TIMESTAMPTZ, then return TIMESTAMPTZ.
• If the first parameter is DATETIME, then return DATETIME.

Special cases:

• If <minutes> is negative, the function behaves the same as adding the corresponding minutes to the base time (i.e., MINUTES_SUB(date, -n) is equivalent to MINUTES_ADD(date, n)).
• If the input is of DATE type (only includes year, month, and day), its time part defaults to 00:00:00.
• If the input datetime includes microseconds, the original microsecond precision is preserved after subtracting minutes (e.g., '2023-01-01 00:01:00.123456' becomes '2023-01-01 00:00:00.123456' after subtracting 1 minute).
• If the calculation result exceeds the valid range of the DATETIME type (0000-01-01 00:00:00 to 9999-12-31 23:59:59.999999), an exception is thrown.
• If any parameter is NULL, returns NULL.

Examples

-- Subtract minutes from DATETIME
SELECT MINUTES_SUB('2020-02-02 02:02:02', 1) AS result;
+---------------------+
| result              |
+---------------------+
| 2020-02-02 02:01:02 |
+---------------------+

-- Time with microseconds (preserves precision)
SELECT MINUTES_SUB('2023-07-13 22:38:18.456789', 10) AS result;
+----------------------------+
| result                     |
+----------------------------+
| 2023-07-13 22:28:18.456789 |
+----------------------------+

-- Negative minutes (equivalent to addition)
SELECT MINUTES_SUB('2023-07-13 22:23:18', -5) AS result;
+---------------------+
| result              |
+---------------------+
| 2023-07-13 22:28:18 |
+---------------------+

-- Input is of DATE type (default time 00:00:00)
SELECT MINUTES_SUB('2023-07-13', 30) AS result;
+---------------------+
| result              |
+---------------------+
| 2023-07-12 23:30:00 |
+---------------------+

-- Example of TimeStampTz type, SET time_zone = '+08:00'
SELECT MINUTES_SUB('2025-10-10 11:22:33.123+07:00', 1);
+-------------------------------------------------+
| MINUTES_SUB('2025-10-10 11:22:33.123+07:00', 1) |
+-------------------------------------------------+
| 2025-10-10 12:21:33.123+08:00                   |
+-------------------------------------------------+

-- Any parameter is NULL, returns NULL
SELECT MINUTES_SUB(NULL, 10), MINUTES_SUB('2023-07-13 22:28:18', NULL) AS result;
+-----------------------+--------+
| MINUTES_SUB(NULL, 10) | result |
+-----------------------+--------+
| NULL                  | NULL   |
+-----------------------+--------+


-- Calculation result exceeds datetime range, throws error
SELECT MINUTES_SUB('0000-01-01 00:00:00', 1) AS result;
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation minute_add of 0000-01-01 00:00:00, -1 out of range