SQL HOUR_MINUTE

What is SQL HOUR_MINUTE?

HOUR_MINUTE is a MySQL interval unit that represents a duration of hours and minutes in the format 'HH:MM' for temporal arithmetic and extraction.

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 HOUR_MINUTE: Supported: MySQL 5.0+, MariaDB 5.3+ Not Supported: PostgreSQL, SQL Server, Oracle, SQLite

SQL HOUR_MINUTE Full Explanation

In MySQL and MariaDB, HOUR_MINUTE is one of the composite temporal interval units. It treats a literal string such as '2:30' as 2 hours and 30 minutes. You can add or subtract this interval to and from DATE, DATETIME, or TIMESTAMP values with functions like DATE_ADD() and DATE_SUB(), or you can extract it from a temporal expression with the EXTRACT() function. HOUR_MINUTE cannot be used as the first argument of TIMESTAMPDIFF(); only single-part units are accepted there. When supplied as an INTERVAL, the literal must be either a quoted string in 'HH:MM' format or an integer formatted as HHMM.

SQL HOUR_MINUTE Syntax

-- Add an hour-minute interval
DATE_ADD(date_expr, INTERVAL expr HOUR_MINUTE);

-- Subtract an hour-minute interval
DATE_SUB(date_expr, INTERVAL expr HOUR_MINUTE);

-- Extract hours and minutes as a composite value
EXTRACT(HOUR_MINUTE FROM datetime_expr);

SQL HOUR_MINUTE Parameters

Example Queries Using SQL HOUR_MINUTE

-- Add 1 hour 10 minutes to a timestamp
SELECT DATE_ADD('2024-04-15 09:05:00', INTERVAL '1:10' HOUR_MINUTE);

-- Subtract 45 minutes (0 hours 45 minutes)
SELECT DATE_SUB('2024-04-15 09:05:00', INTERVAL '0:45' HOUR_MINUTE);

-- Use integer literal instead of string (2 hours 5 minutes)
SELECT DATE_ADD('2024-04-15 09:05:00', INTERVAL 205 HOUR_MINUTE);

-- Extract as a four-digit integer (HHMM)
SELECT EXTRACT(HOUR_MINUTE FROM '2024-04-15 09:05:00');

Expected Output Using SQL HOUR_MINUTE

Each query returns a DATETIME value with the specified hours and minutes added or subtracted, or a numeric HHMM integer when using EXTRACT

Use Cases with SQL HOUR_MINUTE

Adjust timestamps by precise hour-minute offsets
Calculate scheduling windows such as "+1:30" for meeting reminders
Store human-readable duration settings (e.g., "02:15") and apply them in queries
Extract combined hours and minutes from timestamps for display or reporting

Common Mistakes with SQL HOUR_MINUTE

Using HOUR_MINUTE with TIMESTAMPDIFF() (only single-part units allowed)
Omitting quotes around a literal containing a colon; unquoted strings with a colon cause a syntax error
Swapping minutes and hours (INTERVAL '30:02' HOUR_MINUTE means 30 hours, 2 minutes)

First Introduced In

MySQL 5.0

Frequently Asked Questions

What is the format of an HOUR_MINUTE literal?

Use either a quoted string 'HH:MM' or a four-digit integer HHMM. For example, '2:45' and 245 both represent 2 hours and 45 minutes.

Can I add negative HOUR_MINUTE intervals?

Yes. Prepend a minus sign: `DATE_ADD(ts, INTERVAL -'1:15' HOUR_MINUTE)` subtracts 1 hour 15 minutes.

Does HOUR_MINUTE work with TIME columns?

Absolutely. You can add or subtract an HOUR_MINUTE interval to TIME values just like with DATETIME or TIMESTAMP.

How do I extract hours and minutes separately?

Use EXTRACT(HOUR FROM datetime) and EXTRACT(MINUTE FROM datetime) or apply division/modulus to the combined HOUR_MINUTE integer.