SQL HOUR_MICROSECOND

What is the SQL HOUR_MICROSECOND interval unit?

HOUR_MICROSECOND is a MySQL temporal interval unit that represents a composite value of hours, minutes, seconds, and microseconds for date-time 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_MICROSECOND: Supported: MySQL 5.6+, MariaDB 10.0+ Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Standard SQL

SQL HOUR_MICROSECOND Full Explanation

In MySQL and MariaDB, HOUR_MICROSECOND is one of the multi-part interval units accepted by functions such as DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), TIMESTAMPDIFF(), and EXTRACT(). It encodes four time components—hours, minutes, seconds, and microseconds—into a single literal, enabling high-precision calculations down to one-millionth of a second. The literal must follow the format 'HHHHHH:MM:SS.microseconds', where:• HHHHHH is one to six digits of hours (leading zeros optional)• MM is two-digit minutes (00-59)• SS is two-digit seconds (00-59)• microseconds is six digits (000000-999999)When used with DATE_ADD() or DATE_SUB(), the literal adjusts a DATETIME or TIMESTAMP value by the specified interval. With EXTRACT(), MySQL returns the composite value as an integer (HHMMSSuuuuuu) for analytical queries. Because the unit bundles multiple fields, it eliminates the need to add each component separately and preserves microsecond precision in a single operation.Caveats:• The microseconds portion is truncated if the source column lacks fractional seconds.• Invalid minute/second ranges (≥60) raise ER_BAD_TIME_ERROR.• HOUR_MICROSECOND is not part of the ANSI SQL standard and is unsupported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL HOUR_MICROSECOND Syntax

-- Date arithmetic
DATE_ADD(date_expr, INTERVAL 'HH:MM:SS.micro' HOUR_MICROSECOND);
DATE_SUB(date_expr, INTERVAL 'HH:MM:SS.micro' HOUR_MICROSECOND);

-- Extraction
EXTRACT(HOUR_MICROSECOND FROM time_expr);

SQL HOUR_MICROSECOND Parameters

Example Queries Using SQL HOUR_MICROSECOND

-- 1. Add 1 hour, 2 minutes, 3.400500 seconds
SELECT DATE_ADD('2023-10-15 08:30:00.000000',
       INTERVAL '1:02:03.400500' HOUR_MICROSECOND) AS new_time;

-- 2. Subtract high-precision interval
SELECT DATE_SUB('2023-10-15 08:30:00.000000',
       INTERVAL '0:10:00.123456' HOUR_MICROSECOND) AS earlier_time;

-- 3. Extract composite value
SELECT EXTRACT(HOUR_MICROSECOND
       FROM '12:05:30.999888') AS composite_int;

Expected Output Using SQL HOUR_MICROSECOND

Returns '2023-10-15 09:32:03.400500'.
Returns '2023-10-15 08:20:00.876544'.
Returns integer 120530999888 (HHMMSSuuuuuu).

Use Cases with SQL HOUR_MICROSECOND

Schedule or log events that require sub-second accuracy.
Measure API latency or job runtimes with microsecond precision.
Simplify adding or subtracting multi-field time intervals in ETL pipelines.

Common Mistakes with SQL HOUR_MICROSECOND

Supplying an interval with invalid ranges, e.g., '1:65:00.000000'.
Omitting the microseconds dot separator ('1:02:03' vs '1:02:03.000000').
Using HOUR_MICROSECOND in unsupported databases.
Expecting implicit rounding instead of truncation when the target column lacks fractional seconds.

First Introduced In

MySQL 5.6

Frequently Asked Questions

What format does HOUR_MICROSECOND use?

Use 'HHHHHH:MM:SS.microseconds'. The microseconds part must be six digits.

Is HOUR_MICROSECOND ANSI-standard SQL?

No. It is specific to MySQL and MariaDB.

What happens if my DATETIME column has no fractional seconds?

MySQL stores the date-time but silently truncates the microseconds portion.

How do I extract hours, minutes, seconds, and microseconds together?

Use EXTRACT(HOUR_MICROSECOND FROM time_expr) to return an integer like 120530999888.