SQL DAY_SECOND

What is SQL DAY_SECOND?

DAY_SECOND represents an interval that spans days, hours, minutes, and seconds, enabling fine-grained 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_SECOND: MySQL, MariaDB, Oracle, PostgreSQL (as part of INTERVAL literal), SQL Standard (ANSI), Snowflake

SQL DAY_SECOND Full Explanation

DAY_SECOND is an interval qualifier that combines the DAY, HOUR, MINUTE, and SECOND fields into a single value. You use it in functions such as DATE_ADD, DATE_SUB, TIMESTAMPADD, and TIMESTAMPDIFF (MySQL) or in ANSI/Oracle interval literals (INTERVAL 'n' DAY TO SECOND). Internally the database converts the literal string into a signed integer representing the total number of seconds, then adds or subtracts that value from the originating date-time. Precision can reach fractional seconds depending on dialect support. Because DAY_SECOND collapses multiple temporal parts into one token, it simplifies adding or subtracting mixed-unit durations without chaining multiple statements. Caveats: the literal must match the expected format ('D HH:MM:SS[.fraction]'); out-of-range components throw errors; negative values require a leading '-' sign.

SQL DAY_SECOND Syntax

-- MySQL / MariaDB date arithmetic
DATE_ADD(date_expr, INTERVAL expr DAY_SECOND);
DATE_SUB(date_expr, INTERVAL expr DAY_SECOND);
TIMESTAMPADD(DAY_SECOND, expr, timestamp_expr);

-- ANSI/Oracle literal
INTERVAL 'd hh:mm:ss[.ffff]' DAY TO SECOND

SQL DAY_SECOND Parameters

expr (string or integer) - The interval literal in 'D HH:MM:SS' or similar format depending on dialect.
date_expr / timestamp_expr (DATETIME/TIMESTAMP) - The starting point for the calculation.

Example Queries Using SQL DAY_SECOND

-- Add 2 days, 3 hours, 15 minutes, 10 seconds
SELECT DATE_ADD('2024-01-10 08:00:00', INTERVAL '2 03:15:10' DAY_SECOND);

-- Subtract 5 hours, 30 minutes from now()
SELECT DATE_SUB(NOW(), INTERVAL '0 05:30:00' DAY_SECOND);

-- Oracle style interval literal
SELECT TIMESTAMP '2024-01-10 08:00:00' + INTERVAL '2 03:15:10' DAY TO SECOND FROM dual;

Expected Output Using SQL DAY_SECOND

Each query returns a TIMESTAMP or DATETIME shifted forward or backward by the specified mix of days, hours, minutes, and seconds

Use Cases with SQL DAY_SECOND

Shifting a timestamp by a mixed duration in one step
Calculating SLAs such as "resolve within 2 days 4 hours"
Building time-windowed reports (e.g., last 1 day 6 hours)
Scheduling retry logic with sub-day precision

Common Mistakes with SQL DAY_SECOND

Supplying an interval string in the wrong format ('HH:MM:SS' without day when required)
Forgetting the single quotes around the literal
Mixing up DATE_ADD vs TIMESTAMPADD across dialects
Assuming fractional seconds are always supported

First Introduced In

SQL:1999 interval spec; MySQL 4.1

Frequently Asked Questions

What is an interval with DAY_SECOND precision?

An interval with DAY_SECOND precision contains a number of days plus hours, minutes, and seconds, making it ideal for representing mixed-unit durations like "2 days 3 hours".

Does DAY_SECOND accept fractional seconds?

In MySQL you can add fractional seconds up to microsecond precision: '0 00:00:01.500' DAY_SECOND adds 1.5 seconds. Oracle allows up to 9 digits of fractional seconds.

Can I subtract a DAY_SECOND interval?

Yes - either wrap the literal in a negative sign (INTERVAL '-1 02:00:00' DAY_SECOND) or use DATE_SUB/TIMESTAMPDIFF depending on your dialect.

What happens if the interval string is malformed?

The database raises an error such as "Incorrect interval value" or "invalid day-second interval literal"; nothing is executed.