INTERVAL TYPE

Data Types
Buy Me A Coffee

A data type representing a span of time (e.g., '3 days', '2 hours 30 minutes') that can be stored, compared, and used in date arithmetic.

Stores a duration of time (days, months, years, hours, minutes, seconds). Useful for date arithmetic: current_date plus interval 7 days. Precision and unit handling vary -- verify behavior with your database interval arithmetic.

Compatibility

Filter by Database
SQL INTERVAL TYPE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 5.0 INTERVAL is a keyword used in date arithmetic expressions but is not a storable column type. Store durations as INT (seconds) or separate numeric columns.
PostgreSQL Native 7.4 INTERVAL is a fully storable column type. Supports ISO 8601 and verbose input formats. Arithmetic with timestamp/date produces and consumes INTERVAL values natively.
SQL Server Not Supported - No INTERVAL type or keyword. Date arithmetic uses DATEADD() and DATEDIFF() functions. Durations must be stored as numeric columns.
Oracle Native 9i Two storable interval types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. They are not interchangeable and cannot be mixed in arithmetic.
SQLite Not Supported - No INTERVAL type. Date arithmetic uses the date()/datetime() functions with modifier strings like '+7 days'. Store intervals as integer seconds or text.

Details

PostgreSQL has the most complete INTERVAL implementation — it is a first-class storable type with rich literal syntax and seamless arithmetic with timestamps. Oracle's two-type split (YEAR TO MONTH vs DAY TO SECOND) reflects the genuine semantic problem that a month is not a fixed number of days; the two types cannot be mixed in arithmetic. MySQL uses INTERVAL only as an arithmetic keyword, not a storable type — a frequently missed distinction. SQL Server and SQLite have no interval concept at all; developers store durations as integers and compute with DATEADD/DATEDIFF or SQLite's string modifier functions. The lack of a storable interval type in three of five engines makes this a portability challenge for applications that need to persist duration values such as SLAs, scheduling windows, or rate limit windows.

Standard Syntax

-- PostgreSQL SELECT NOW() + INTERVAL '3 days'; SELECT INTERVAL '2 hours 30 minutes';

Version Support

MySQL: Native since 5.0 PostgreSQL: Native since 7.4 SQL Server: Not supported Oracle: Native since 9i SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

INTERVAL is a keyword used in date arithmetic expressions but is not a storable column type. Store durations as INT (seconds) or separate numeric columns.

-- Date arithmetic only: SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders; SELECT NOW() + INTERVAL '3' HOUR;

PostgreSQL Native syntax

INTERVAL is a fully storable column type. Supports ISO 8601 and verbose input formats. Arithmetic with timestamp/date produces and consumes INTERVAL values natively.

CREATE TABLE sla (response_target INTERVAL NOT NULL); INSERT INTO sla VALUES (INTERVAL '4 hours'); SELECT created_at + response_target FROM sla;

SQL Server Alternative syntax

No INTERVAL type or keyword. Date arithmetic uses DATEADD() and DATEDIFF() functions. Durations must be stored as numeric columns.

SELECT DATEADD(DAY, 7, order_date) FROM orders; SELECT DATEDIFF(HOUR, start_time, end_time) FROM events;

Oracle Native syntax

Two storable interval types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. They are not interchangeable and cannot be mixed in arithmetic.

CREATE TABLE sla ( response_target INTERVAL DAY TO SECOND NOT NULL ); INSERT INTO sla VALUES (INTERVAL '0 04:00:00' DAY TO SECOND); SELECT order_date + INTERVAL '7' DAY FROM orders;

SQLite Alternative syntax

No INTERVAL type. Date arithmetic uses the date()/datetime() functions with modifier strings like '+7 days'. Store intervals as integer seconds or text.

SELECT date(order_date, '+7 days') FROM orders; SELECT datetime('now', '-3 hours');