SQL DAY_HOUR

What is SQL DAY_HOUR?

MySQL interval unit that represents a combined number of days and hours for date calculations 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 DAY_HOUR: Supported: MySQL 4.1+, MariaDB 5.3+ Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Standard SQL

SQL DAY_HOUR Full Explanation

DAY_HOUR is a composite interval unit available in MySQL and MariaDB. It lets you work with the day and hour portions of a datetime value as a single numeric field. You can:1. Extract the combined value (in the form DDHH) from a DATETIME or TIMESTAMP using EXTRACT().2. Add or subtract an interval expressed in days and hours using DATE_ADD(), DATE_SUB(), or the INTERVAL keyword.Internally, MySQL stores the interval as a four-digit decimal where the first two digits are days (00-99) and the last two digits are hours (00-23). For arithmetic, MySQL converts the supplied value into the equivalent number of seconds before performing the operation.Caveats:- The hours component must be 00-23; anything higher triggers ER_WRONG_VALUE_FOR_TYPE.- Leading zeros are required when either days or hours is a single digit (e.g., '0307' means 3 days and 7 hours).- DAY_HOUR cannot be used with DATE_FORMAT(); it is only valid with EXTRACT, DATE_ADD, DATE_SUB, and TIMESTAMPDIFF.- Unsupported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL DAY_HOUR Syntax

-- Extract
EXTRACT(DAY_HOUR FROM datetime_expr);

-- Date arithmetic
DATE_ADD(datetime_expr, INTERVAL expr DAY_HOUR);
DATE_SUB(datetime_expr, INTERVAL expr DAY_HOUR);

-- TIMESTAMPDIFF (returns integer difference)
TIMESTAMPDIFF(DAY_HOUR, datetime_expr1, datetime_expr2);

SQL DAY_HOUR Parameters

datetime_expr DATETIME (TIMESTAMP) - DATE|||The value to operate on.
expr (CHAR) - INT|||Four-digit literal or column containing DDHH.

Example Queries Using SQL DAY_HOUR

-- 1. Extract  (returns 104 as INTEGER: 1 day 4 hours)
SELECT EXTRACT(DAY_HOUR FROM '2024-06-15 04:22:00') AS day_hour_val;

-- 2. Add 3 days 7 hours
SELECT DATE_ADD('2024-06-15 04:22:00', INTERVAL 0307 DAY_HOUR) AS new_time;

-- 3. Subtract 2 days 12 hours
SELECT DATE_SUB(NOW(), INTERVAL 0212 DAY_HOUR) AS earlier;

-- 4. Difference between two timestamps in day_hour units
SELECT TIMESTAMPDIFF(DAY_HOUR, '2024-06-10 08:00:00', '2024-06-13 11:00:00') AS diff_dh;

Expected Output Using SQL DAY_HOUR

Returns integer 104.
Returns '2024-06-18 11:22:00'.
Returns a timestamp 2 days and 12 hours earlier than the current time.
Returns 327 (3 days 3 hours).

Use Cases with SQL DAY_HOUR

Extract day and hour portions in a single step for reporting or grouping.
Add mixed day and hour offsets without splitting them into separate calls.
Calculate elapsed time between events when both day and hour resolution is sufficient.

Common Mistakes with SQL DAY_HOUR

Forgetting leading zeros (writing 37 instead of 0307).
Supplying a value where hours exceed 23.
Expecting it to work in non-MySQL databases.
Using DAY_HOUR with DATE_FORMAT() or in a column definition.

First Introduced In

MySQL 4.1.1

Frequently Asked Questions

What does 0307 represent in a DAY_HOUR expression?

It represents 3 days and 7 hours. The first two digits are days, the last two are hours.

Can I use fractional hours like 3.5 in DAY_HOUR?

No. DAY_HOUR only accepts whole hours (00-23). Use separate DAY and HOUR arithmetic or convert to minutes.

How do I extract only the hour after using DAY_HOUR?

DAY_HOUR returns a four-digit integer. Apply MOD(value, 100) to isolate the hour component.

Is DAY_HOUR part of the ANSI SQL standard?

No. It is a MySQL extension. Other engines use different interval syntax or require two fields.