SQL DAY_MICROSECOND

What is the SQL DAY_MICROSECOND interval unit?

Composite interval unit in MySQL that expresses a period containing days, hours, minutes, seconds, and microseconds for date 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.

SQL DAY_MICROSECOND Full Explanation

DAY_MICROSECOND is a MySQL-specific temporal unit used with functions such as DATE_ADD, DATE_SUB, TIMESTAMPDIFF, EXTRACT, and CAST ... AS INTERVAL. It represents a span of time that includes:- Days (0-34, or larger when SQL mode ALLOW_INVALID_DATES is off)- Hours (0-23)- Minutes (0-59)- Seconds (0-59)- Microseconds (0-999999)The literal value must be written as a string in the format 'DD HH:MM:SS[.microseconds]'. When supplied to date-arithmetic functions, MySQL converts the value to an internal integer number of microseconds and adds or subtracts it from the given DATETIME or TIMESTAMP. When used with EXTRACT, it returns an integer constructed as DHHMMSSUUUUUU (for example, 2 hours → 002000000000). Microseconds are available only in MySQL 5.6.4 and later. If the literal is malformed, MySQL returns NULL and emits a warning.

SQL DAY_MICROSECOND Syntax

-- Add an interval
DATE_ADD(datetime_expr,
         INTERVAL 'DD HH:MM:SS[.fraction]' DAY_MICROSECOND);

-- Subtract an interval
DATE_SUB(datetime_expr,
         INTERVAL 'DD HH:MM:SS[.fraction]' DAY_MICROSECOND);

-- Extract the composite value
EXTRACT(DAY_MICROSECOND FROM datetime_expr);

-- Explicit interval cast
CAST('DD HH:MM:SS[.fraction]' AS INTERVAL DAY_MICROSECOND);

SQL DAY_MICROSECOND Parameters

datetime_expr (DATETIME / TIMESTAMP) - The date or timestamp to operate on
interval_str (VARCHAR) - A string formatted as 'DD HH|||MM|||SS[.microseconds]' representing the span
unit (KEYWORD) - The literal keyword DAY_MICROSECOND identifying the composite unit

Example Queries Using SQL DAY_MICROSECOND

-- 1) Add 1 day, 02:03:04.123456 to a timestamp
SELECT DATE_ADD('2024-01-01 00:00:00',
                INTERVAL '1 02:03:04.123456' DAY_MICROSECOND) AS new_ts;

-- 2) Subtract 10 microseconds from now()
SELECT DATE_SUB(NOW(6),
                INTERVAL '0 00:00:00.000010' DAY_MICROSECOND) AS slightly_before;

-- 3) Extract the composite field from a datetime
SELECT EXTRACT(DAY_MICROSECOND FROM '2024-05-12 14:05:07.500123') AS dm_val;

-- 4) Use CAST for validation
SELECT CAST('3 00:00:00' AS INTERVAL DAY_MICROSECOND) AS ok_interval;

Expected Output Using SQL DAY_MICROSECOND

1) Returns '2024-01-02 02:03:04
123456'
2) Returns the current timestamp minus 10 microseconds
3) Returns 0140507500123 (one day, 14h, 05m, 07s, 500123µs encoded as an integer)
4) Returns the internal interval value 3 00:00:00

Use Cases with SQL DAY_MICROSECOND

Adding or subtracting precise multi-component intervals in scheduling applications
Logging systems that require microsecond-level roll-ups across day boundaries
ETL pipelines needing composite interval calculations in a single literal
Validating interval input strings through CAST before applying them

Common Mistakes with SQL DAY_MICROSECOND

Forgetting the space between day and time portions (use '1 02:03:04', not '102:03:04')
Omitting the quotes around the interval literal
Supplying more than 24 hours in the HH field (exceeding range triggers NULL)
Expecting DAY_MICROSECOND to work outside MySQL/MariaDB
Using it without enabling microsecond precision (MySQL pre-5.6.4 truncates fractions)

First Introduced In

MySQL 5.6.4 (microsecond support added)

Frequently Asked Questions

What format does DAY_MICROSECOND expect?

Use a quoted string like 'DD HH:MM:SS[.microseconds]'. Example: '1 02:03:04.123456'.

Can I use DAY_MICROSECOND outside MySQL?

No. It is only implemented in MySQL and MariaDB. Other databases have different interval syntaxes.

Does DAY_MICROSECOND support negative intervals?

Yes. Prefix the literal with a minus sign ('-1 00:00:00') or subtract it with DATE_SUB.

How precise is DAY_MICROSECOND?

Up to six fractional digits (microseconds) when running on MySQL 5.6.4 or later.