INTERVAL
SQL · Date Functions
Theory
An INTERVAL is a value that represents a length of time, such as 30 days, 6 months or 2 hours, rather than a point on the calendar. You add it to or subtract it from a date or timestamp: hire_date + INTERVAL '1 year' gives the work anniversary, and login_at - INTERVAL '15 minutes' gives a moment just before login.
In PostgreSQL, intervals are a real data type and you can even store them in columns or get one back by subtracting two timestamps. MySQL uses INTERVAL only inside expressions, written without quotes: d + INTERVAL 1 YEAR. SQL Server and Snowflake mostly use DATEADD instead, and SQLite expresses the same idea with modifier strings such as date(d, '+6 months') or datetime(t, '+2 hours'). Note that months and years are not fixed lengths: one month after 15 January is 15 February (31 days), but after 15 February it is 15 March (28 or 29 days).
Why we use it
Time spans appear everywhere: subscriptions last 1 year, sessions expire after 30 minutes, SLAs allow 48 hours. INTERVAL lets you write these spans directly and readably, and the database applies calendar rules correctly.
When to use it
Use INTERVAL arithmetic in PostgreSQL, MySQL and Oracle for shifting dates and building time windows. In SQL Server or Snowflake, use DATEADD; in SQLite, use date()/datetime() modifiers. Use a plain number of days only when your dialect documents it.
How it works
- Start with a date or timestamp
- Write the span: INTERVAL '6 months', '2 hours'
- Span in months or years? Yes: Move on the calendar (same day number). No: Add an exact length.
- Return the shifted date or timestamp
- Use it in SELECT, WHERE or a default
Syntax
date + INTERVAL 'n unit'Example
SELECT hire_date + INTERVAL '1 year' FROM employees;Key points
- Postgres/MySQL syntax shown; SQLite uses modifier strings instead
- Supports years, months, days, hours, minutes, seconds
- MySQL writes INTERVAL 1 YEAR (no quotes); PostgreSQL writes INTERVAL '1 year'
- Months and years have variable length — 1 month is not always 30 days
- In PostgreSQL, timestamp - timestamp returns an interval value