SQL INTERVAL

What does the SQL INTERVAL keyword do?

INTERVAL represents a span of time that can be added to or subtracted from date and timestamp values.

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 INTERVAL: PostgreSQL, MySQL (8.0+), MariaDB, Oracle, IBM Db2, Snowflake, BigQuery, DuckDB. SQL Server uses DATETIMEOFFSET and explicit DATEADD instead of INTERVAL.

SQL INTERVAL Full Explanation

INTERVAL is both a data type and a literal that encodes a duration such as 3 days, 2 hours 15 minutes, or 1 year 6 months. It allows arithmetic on DATE, TIME, and TIMESTAMP columns without manually converting units. In most engines, an INTERVAL literal is written in quotes, paired with a unit keyword (for example, INTERVAL '7 day'). The engine converts that literal to its internal interval type and supports addition, subtraction, comparisons, casting, and storage. Two styles exist: year-to-month intervals (years, months) and day-to-second intervals (days down to fractional seconds). Precision and leading field specifications are optional but let you trim storage and validate range boundaries. Caveats: not all dialects store INTERVAL columns natively; some only allow literals in expressions. Mixed-type arithmetic (such as adding a year-to-month interval to a day-to-second interval) is disallowed. Always quote the value portion, ensure the unit is valid for the dialect, and prefer explicit CAST for portability.

SQL INTERVAL Syntax

-- Literal
INTERVAL 'quantity unit'

-- Column definition
col_name INTERVAL [field_to] [precision]

-- Arithmetic example
SELECT order_date + INTERVAL '3 day' AS ship_by
FROM   orders;

SQL INTERVAL Parameters

quantity (numeric) - the length of time
unit (keyword) - time unit such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
field_to (keyword) - optional ending field (e.g., YEAR TO MONTH, DAY TO SECOND)
precision (integer) - optional number of digits for leading field or fractional seconds

Example Queries Using SQL INTERVAL

-- Add five minutes to a timestamp
SELECT login_ts + INTERVAL '5 minute' AS auto_logout
FROM   sessions;

-- Find orders older than 30 days
SELECT *
FROM   orders
WHERE  order_date < CURRENT_DATE - INTERVAL '30 day';

-- Define a column storing durations
CREATE TABLE task (
  id            SERIAL PRIMARY KEY,
  description   TEXT,
  expected_run  INTERVAL DAY TO SECOND
);

-- Aggregate total downtime per server
SELECT server_id,
       SUM(end_ts - start_ts) AS total_downtime
FROM   outages
GROUP  BY server_id;

Expected Output Using SQL INTERVAL

Each query returns a time-shifted or time-filtered result set
Column definitions create an INTERVAL-typed column
Aggregations output an interval representing summed durations

Use Cases with SQL INTERVAL

Schedule calculations such as "ship in 2 days" or "expire in 90 seconds"
Age or retention queries (records older than INTERVAL '6 month')
Measuring elapsed time between two timestamps
Storing process runtimes, SLAs, or TTLs in an INTERVAL column
Windowing event-stream data (e.g., INTERVAL '15 minute' tumbling windows)

Common Mistakes with SQL INTERVAL

Omitting quotes around the literal (INTERVAL 5 day is invalid)
Using plural units incorrectly in some dialects (PostgreSQL allows both, MySQL requires singular)
Mixing year-to-month with day-to-second intervals
Forgetting to cast a string to INTERVAL when binding parameters
Assuming all databases support the WEEK unit (not standard)

First Introduced In

SQL-92

Frequently Asked Questions

What units can I use inside an INTERVAL?

Typical units are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, and YEAR. Availability varies by dialect.

Can I store INTERVAL values in a table?

Yes. Define a column such as duration INTERVAL DAY TO SECOND. Some databases (e.g., MySQL) only allow expressions, not storage.

How do I subtract one timestamp from another?

Simply subtract: end_ts - start_ts returns an INTERVAL (or numeric milliseconds in some dialects).

Is INTERVAL ANSI-standard?

Yes. The INTERVAL data type and literals were introduced in SQL-92, though implementation details differ across vendors.