Shape / columns · All operations

Shift a date by an interval

A variant of Parse a date or time

Add or subtract an amount of time to a date, time or timestamp.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Add or subtract an interval

SQL
CREATE TEMP TABLE result AS
SELECT DATE '2026-01-31' + INTERVAL 1 MONTH AS next_month, DATE '2026-03-01' - INTERVAL 1 DAY AS day_before,
       TIMESTAMP '2026-02-10 23:00:00' + INTERVAL 90 MINUTE AS later, DATE '2024-02-29' + INTERVAL 1 YEAR AS next_year;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT next_month = TIMESTAMP '2026-02-28 00:00:00' AND day_before = TIMESTAMP '2026-02-28 00:00:00' AND later = TIMESTAMP '2026-02-11 00:30:00' AND next_year = TIMESTAMP '2025-02-28 00:00:00' FROM result)) THEN 'as expected' ELSE error('a month, a day, 90 minutes and a year were not added or subtracted to the dates as expected') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a month, a day, 90 minutes and a year were not added or subtracted to the dates as expected”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (1)
  • Date Addread 2026-09-25

    add an interval to a date

Ibis (6)
  • DateAddread 2026-09-27

    Add an interval to a date

  • DateSubread 2026-09-27

    Subtract an interval from a date

  • TimeAddread 2026-09-27

    Add an interval to a time

  • TimeSubread 2026-09-27

    Subtract an interval from a time

  • TimestampAddread 2026-09-27

    Add an interval to a timestamp

  • TimestampSubread 2026-09-27

    Subtract an interval from a timestamp

KNIME Analytics Platform (2)
  • Date Shifterread 2026-09-27

    Shift dates by a duration or a number of granularity units

  • Time Shifterread 2026-09-27

    Shift times by a duration or a number of granularity units

RapidMiner Studio (1)
  • Adjust Dateread 2026-09-27

    Shift dates by adding or subtracting an amount of time

Background

In DuckDB, an INTERVAL can be added to or subtracted from a TIMESTAMP, TIMESTAMPTZ, DATE or TIME value with the + and - operators.[1] In DuckDB, adding an INTERVAL to a DATE returns a TIMESTAMP, even when the interval has no time part.[1]

When to use it

In PostgreSQL, adding months keeps the day of the month unless it would be past the end of the new month, when the last day of that month is used: March 31 plus 1 month is April 30.[2] In PostgreSQL, March 31 plus 2 months is May 31.[2] Add the full number of months to the starting date in one step rather than chaining one-month additions. (Meridian's view)

In PostgreSQL, adding interval '1 day' to a timestamp with time zone across a daylight saving change keeps the local time of day, while adding interval '24 hours' does not.[2] In DuckDB, when adding a calendar interval to a TIMESTAMPTZ lands on a local time that does not exist during a daylight saving transition, the result is moved forward to the next valid timestamp, following PostgreSQL.[3] Shift by days and months when the meaning is a date on the calendar, and by hours when the meaning is an elapsed time. (Meridian's view)

In DuckDB, TIMESTAMP '2000-02-01' plus the difference between TIMESTAMP '2000-02-01' and TIMESTAMP '2000-01-01' gives 2000-03-03, not 2000-03-01.[1]

The method

PostgreSQL handles an interval's months, days and microseconds fields in turn: the months first, then the days, and finally the microseconds, added literally.[2]

In DuckDB, adding an integer to a DATE adds that many days, so DATE '1992-03-22' + 5 is 1992-03-27.[4] In DuckDB, subtracting an interval from a TIME wraps around the day, so TIME '02:00:00' - INTERVAL '3 days 23 hours' equals TIME '03:00:00'.[1] In DuckDB, adding to or subtracting from an infinite date produces the same infinite value.[4]

Sources

  1. [1]Interval Type – DuckDBdocumentation
  2. [2]PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operatorsdocumentation
  3. [3]Timestamp with Time Zone Functions – DuckDBdocumentation
  4. [4]Date Functions – DuckDBdocumentation