MINUTES_DIFF

Description

The MINUTES_DIFF function calculates the difference in minutes between two datetime values. The result is the number of minutes obtained by subtracting the start time from the end time. This function supports DATE, DATETIME, and TIMESTAMP_NS.

Syntax

MINUTES_DIFF(`<date_or_time_expr1>`, `<date_or_time_expr2>`)

Parameters

Parameter	Description
<date_or_time_expr1>	The end time, of type DATE, DATETIME, or TIMESTAMP_NS.
<date_or_time_expr2>	The start time, of type DATE, DATETIME, or TIMESTAMP_NS.

Return Value

Returns an INT type integer representing the minute difference between <date_or_time_expr1> and <date_or_time_expr2> (1 hour = 60 minutes).

• If <date_or_time_expr1> is later than <date_or_time_expr2>, returns a positive number.
• If <date_or_time_expr1> is earlier than <date_or_time_expr2>, returns a negative number.
• The calculation considers the actual difference and does not ignore seconds or microseconds.
• 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 scale, seconds, or microsecond parts that are non-zero, they are not ignored during calculation.
• If any parameter is NULL, returns NULL.

Examples

-- Minute difference when end time is greater than start time
SELECT MINUTES_DIFF('2020-12-25 22:00:00', '2020-12-25 21:00:00') AS result;
+--------+
| result |
+--------+
|     60 |
+--------+

-- Includes scale, calculation does not ignore it
SELECT MINUTES_DIFF('2020-12-25 21:00:00.999', '2020-12-25 22:00:00.923');
+--------------------------------------------------------------------+
| MINUTES_DIFF('2020-12-25 21:00:00.999', '2020-12-25 22:00:00.923') |
+--------------------------------------------------------------------+
|                                                                -59 |
+--------------------------------------------------------------------+

-- End time is earlier than start time, returns negative number
SELECT MINUTES_DIFF('2023-07-13 21:50:00', '2023-07-13 22:00:00') AS result;
+--------+
| result |
+--------+
|    -10 |
+--------+

-- Input is of DATE type (default time 00:00:00)
SELECT MINUTES_DIFF('2023-07-14', '2023-07-13') AS result;
+--------+
| result |
+--------+
|   1440 |
+--------+

-- Two times have different seconds, seconds are also calculated
SELECT MINUTES_DIFF('2023-07-13 22:30:59', '2023-07-13 22:31:01') AS result;
+--------+
| result |
+--------+
|      0 |
+--------+

-- Any parameter is NULL, returns NULL
SELECT MINUTES_DIFF(NULL, '2023-07-13 22:00:00'), MINUTES_DIFF('2023-07-13 22:00:00', NULL) AS result;
+-------------------------------------------+--------+
| MINUTES_DIFF(NULL, '2023-07-13 22:00:00') | result |
+-------------------------------------------+--------+
|                                      NULL |   NULL |
+-------------------------------------------+--------+