SQL MINUTE_SECOND

What is the MINUTE_SECOND interval unit in SQL?

A compound interval unit in MySQL that represents minutes and seconds for date and time arithmetic.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MINUTE_SECOND: MySQL, MariaDB

SQL MINUTE_SECOND Full Explanation

MINUTE_SECOND is one of MySQL’s supported INTERVAL unit keywords. It expresses a duration made up of minutes and seconds and can be used with DATE_ADD, DATE_SUB, TIMESTAMPADD, TIMESTAMPDIFF, and in standalone INTERVAL literals.1. Literal form - Write the value as a quoted string in the format 'MM:SS' (for example '7:30' or '12:05'). - The left part is interpreted as minutes, the right part (00-59) as seconds.2. Behavior in DATE_ADD / DATE_SUB / TIMESTAMPADD - The specified minutes and seconds are added to or subtracted from the input datetime or time value. - Precision stops at whole seconds. Fractional seconds are not accepted with MINUTE_SECOND.3. Behavior in TIMESTAMPDIFF - Returns a BIGINT whose value is minutes*100 + seconds. - Example: 2 minutes 15 seconds becomes 215.4. Caveats - Only supported in MySQL and MariaDB. Other databases require different interval syntax. - Incorrect string format (missing colon or seconds outside 0-59) raises an error or returns NULL depending on SQL mode. - Leading zeros are optional for minutes but required for seconds. - For sub-second precision use SECOND_MICROSECOND instead.

SQL MINUTE_SECOND Syntax

-- As INTERVAL literal
INTERVAL 'm:ss' MINUTE_SECOND

-- With DATE_ADD / DATE_SUB
DATE_ADD(date_expression, INTERVAL 'm:ss' MINUTE_SECOND);
DATE_SUB(date_expression, INTERVAL 'm:ss' MINUTE_SECOND);

-- With TIMESTAMPADD
TIMESTAMPADD(MINUTE_SECOND, minuteSecondValue, date_expression);

-- With TIMESTAMPDIFF
TIMESTAMPDIFF(MINUTE_SECOND, datetime_expr1, datetime_expr2);

SQL MINUTE_SECOND Parameters

Example Queries Using SQL MINUTE_SECOND

-- Add 3 minutes 45 seconds
SELECT DATE_ADD('2023-10-01 08:00:00', INTERVAL '3:45' MINUTE_SECOND) AS new_time;

-- Subtract 30 seconds
SELECT DATE_SUB('2023-10-01 08:00:00', INTERVAL '0:30' MINUTE_SECOND) AS earlier_time;

-- Difference between two timestamps (returns 225)
SELECT TIMESTAMPDIFF(MINUTE_SECOND,
                     '2023-10-01 08:00:00',
                     '2023-10-01 08:03:45') AS diff_ms;

Expected Output Using SQL MINUTE_SECOND

new_time -> 2023-10-01 08:03:45earlier_time -> 2023-10-01 07:59:30diff_ms -> 225 (representing 3 minutes 45 seconds)

Use Cases with SQL MINUTE_SECOND

Add or subtract small durations that cross the minute-second boundary.
Compute precise elapsed time in minutes and seconds between two timestamps.
Cleanly express minute-second offsets without converting to total seconds.

Common Mistakes with SQL MINUTE_SECOND

Omitting the colon in the literal (e.g., '345' instead of '3:45').
Providing seconds greater than 59.
Expecting fractional seconds support.
Using MINUTE_SECOND in databases other than MySQL or MariaDB.
Misinterpreting TIMESTAMPDIFF result 215 as seconds rather than 2 minutes 15 seconds.

First Introduced In

MySQL 4.1

Frequently Asked Questions

How do I format a MINUTE_SECOND literal?

Use a quoted string in the form 'MM:SS', for example '7:30'. The part before the colon is minutes, the part after is seconds (00-59).

Is MINUTE_SECOND supported outside MySQL and MariaDB?

No. PostgreSQL, SQL Server, Oracle, SQLite and most others do not recognize this keyword. They require their own interval syntax.

How does TIMESTAMPDIFF behave with MINUTE_SECOND?

TIMESTAMPDIFF(MINUTE_SECOND, t1, t2) returns an integer minutes*100 + seconds. The value 215 represents 2 minutes and 15 seconds, not 215 seconds.

How can I handle sub-second precision?

MINUTE_SECOND cannot store fractions of a second. Use SECOND_MICROSECOND or perform math on TIME_TO_SEC values if you need millisecond accuracy.