SQL DAY_MINUTE

What is SQL DAY_MINUTE?

DAY_MINUTE is a MySQL interval unit that stores a combined value of days and minutes for date-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 DAY_MINUTE: Supported: MySQL, MariaDB, Percona Server, TiDB. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, standard ANSI SQL.

SQL DAY_MINUTE Full Explanation

DAY_MINUTE is one of the multi-part interval units supported by MySQL. It represents a duration containing two components: the number of days and the number of minutes. The format of the literal is 'D HH:MM', where D is one or more digits for days, HH is two digits for hours (00-23), and MM is two digits for minutes (00-59). You use DAY_MINUTE with functions such as DATE_ADD, DATE_SUB, TIMESTAMPADD, and TIMESTAMPDIFF to shift or compare datetime values. Internally MySQL converts the supplied string into a numeric interval that is added to or subtracted from a date, datetime, or timestamp expression. DAY_MINUTE is not part of the ANSI SQL standard; it is specific to MySQL and compatible forks like MariaDB. Because the literal is passed as a quoted string, leading zeros are required for the hour and minute parts, and any seconds component is not allowed.

SQL DAY_MINUTE Syntax

<date_or_datetime_expression> {+ | -} INTERVAL '<days> <hh>:<mm>' DAY_MINUTE

SQL DAY_MINUTE Parameters

days (INT) - Whole number of days to add or subtract
hh (INT) - Hours component (00-23) embedded in the literal
mm (INT) - Minutes component (00-59) embedded in the literal

Example Queries Using SQL DAY_MINUTE

-- Add 1 day and 45 minutes
SELECT DATE_ADD('2024-04-01 10:15:00', INTERVAL '1 00:45' DAY_MINUTE);

-- Subtract 3 days and 2 minutes
SELECT DATE_SUB('2024-04-05 12:00:00', INTERVAL '3 00:02' DAY_MINUTE);

-- Calculate difference between two timestamps in day-minute units
SELECT TIMESTAMPDIFF(DAY_MINUTE, '2024-04-01 08:00:00', '2024-04-03 09:05:00');

Expected Output Using SQL DAY_MINUTE

First query returns 2024-04-02 11:00:00
Second query returns 2024-04-02 11:58:00
Third query returns 2 01:05 in minutes (converted internally to 2945)

Use Cases with SQL DAY_MINUTE

Adjusting a delivery deadline by an exact number of days and minutes
Rolling forward or back ETL batch timestamps
Measuring elapsed time between two events with day and minute granularity
Cleaning data where day and minute offsets must be merged into a single interval value

Common Mistakes with SQL DAY_MINUTE

Forgetting to quote the interval string
Omitting the leading zeros in HH or MM (e.g., '1 1:5' is invalid)
Assuming seconds are allowed; they are not for DAY_MINUTE
Trying to use DAY_MINUTE in databases that do not support MySQL-specific interval units

First Introduced In

MySQL 3.23

Frequently Asked Questions

What components make up a DAY_MINUTE interval?

A DAY_MINUTE interval stores two parts: days and minutes. The literal format is 'D HH:MM', where D is days, HH hours (00-23), and MM minutes (00-59).

Can I include seconds in a DAY_MINUTE literal?

No. DAY_MINUTE only accepts days, hours, and minutes. If you need seconds use DAY_SECOND or another appropriate unit.

How do I subtract a DAY_MINUTE interval?

Use DATE_SUB or the - operator. Example: SELECT DATE_SUB('2024-04-05 12:00:00', INTERVAL '3 00:02' DAY_MINUTE);

Is DAY_MINUTE portable across databases?

DAY_MINUTE is limited to MySQL and compatible systems. PostgreSQL, Oracle, SQL Server, and SQLite do not recognize this multi-part interval unit.