SQL HOUR_SECOND

What is the HOUR_SECOND interval unit in SQL?

Composite interval unit in MySQL that represents a duration expressed as hours, minutes, and seconds.

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_SECOND: Supported: MySQL 4.1+, MariaDB 5.3+. Not supported: PostgreSQL, SQL Server, Oracle, SQLite.

SQL HOUR_SECOND Full Explanation

HOUR_SECOND is a MySQL-specific interval unit that treats hours, minutes, and seconds as a single composite value. It is accepted anywhere an INTERVAL unit is expected, including DATE_ADD, DATE_SUB, TIMESTAMPDIFF, TIMESTAMPADD (MariaDB), EXTRACT, and arithmetic using the + or - operators. The literal interval value can be written in either 'HH:MM:SS' (colon-separated) or HHMMSS (numeric) format and may include a leading sign. Internally, MySQL converts the supplied text or integer to seconds, performs the requested calculation, and then returns a properly typed DATE, DATETIME, or TIME result depending on the expression. Because the value is parsed as a whole, you cannot omit any component; every literal must include hours, minutes, and seconds. When used with EXTRACT, MySQL returns an integer formatted as HHMMSS (for example, 142338 for 14:23:38). HOUR_SECOND is not part of the ANSI SQL standard and is therefore unavailable in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL HOUR_SECOND Syntax

-- Basic arithmetic
<date_or_datetime> {+ | -} INTERVAL <expr> HOUR_SECOND;

-- With DATE_ADD / DATE_SUB
DATE_ADD(<date_or_datetime>, INTERVAL <expr> HOUR_SECOND);
DATE_SUB(<date_or_datetime>, INTERVAL <expr> HOUR_SECOND);

-- Using EXTRACT
EXTRACT(HOUR_SECOND FROM <datetime>);

SQL HOUR_SECOND Parameters

date_or_datetime (DATETIME/DATE) - The value to add to, subtract from, or extract parts of.
expr (STRING or INT) - Interval literal in 'HH|||MM|||SS' or HHMMSS format; may be signed.
datetime (DATETIME) - Value passed to EXTRACT to retrieve the HHMMSS integer.

Example Queries Using SQL HOUR_SECOND

-- Add 2 hours, 30 minutes, and 45 seconds to the current timestamp
SELECT NOW() + INTERVAL '02:30:45' HOUR_SECOND;

-- Subtract 10 hours and 15 seconds from a specific datetime
SELECT DATE_SUB('2024-05-01 12:00:00', INTERVAL '10:00:15' HOUR_SECOND);

-- Extract hour, minute, and second as a single integer
SELECT EXTRACT(HOUR_SECOND FROM '2024-05-01 14:23:38') AS hhmmss;

Expected Output Using SQL HOUR_SECOND

Returns the current timestamp shifted forward to something like '2024-05-01 02:30:45'.
Returns '2024-04-30 01:59:45'.
Returns the integer 142338 representing 14 hours, 23 minutes, and 38 seconds.

Use Cases with SQL HOUR_SECOND

Add or subtract a mixed time period without chaining multiple INTERVAL clauses.
Quickly calculate time deltas that cross hour boundaries.
Extract HHMMSS in a compact numeric form for reporting or diagnostic purposes.
Build schedules or logs that need fine-grained time arithmetic in a single step.

Common Mistakes with SQL HOUR_SECOND

Forgetting to include minutes or seconds, causing a malformed interval literal error.
Using HOUR_SECOND in databases other than MySQL or MariaDB.
Supplying a value bigger than 838 hours, 59 minutes, 59 seconds, which exceeds MySQL TIME range.
Writing the literal without quotes when it contains colons, leading to a syntax error.

First Introduced In

MySQL 4.1

Frequently Asked Questions

How do I add hours, minutes, and seconds in one statement?

Use HOUR_SECOND with a single INTERVAL expression:```SELECT start_time + INTERVAL '01:45:30' HOUR_SECOND;```

What literal formats are valid for HOUR_SECOND?

'HH:MM:SS' (quoted string) or HHMMSS (numeric). Both may be signed.

Does HOUR_SECOND work in PostgreSQL, SQL Server, or Oracle?

No. It is available only in MySQL and MariaDB. Use separate INTERVAL units or database-specific syntax elsewhere.

Can I use negative HOUR_SECOND intervals?

Yes. Prefix the literal with a minus sign to subtract the composite interval.