Shape / columns · All operations

Work with durations

A variant of Parse a date or time

Parse, format, decompose and compute on durations and intervals as values of their own.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Parse, decompose and compute on durations

SQL
CREATE TEMP TABLE result AS
SELECT CAST('1 day 2 hours 30 minutes' AS INTERVAL) AS parsed, CAST('90 minutes' AS INTERVAL) AS ninety,
       date_part(['day', 'hour', 'minute'], CAST('1 day 2 hours 30 minutes' AS INTERVAL)) AS parts,
       CAST('1 day 2 hours 30 minutes' AS INTERVAL) * 2 AS doubled,
       epoch(INTERVAL '2 hours 30 minutes') AS seconds,
       CAST(CAST('1 day 2 hours 30 minutes' AS INTERVAL) AS VARCHAR) AS shown;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT parsed = INTERVAL '1 day 2 hours 30 minutes' AND ninety = INTERVAL 90 MINUTE AND parts = {'day': 1, 'hour': 2, 'minute': 30} AND doubled = INTERVAL '2 days 5 hours' AND seconds = 9000 AND shown = '1 day 02:30:00' FROM result)) THEN 'as expected' ELSE error('a duration was not parsed, decomposed, doubled, counted in seconds and written out 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 duration was not parsed, decomposed, doubled, counted in seconds and written out 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).

DuckDB (1)
Ibis (5)
KNIME Analytics Platform (4)
Power Query M (1)
  • Duration functionsread 2026-09-27

    13 functions: Duration.From, Duration.FromText, Duration.Days, Duration.TotalHours, Duration.ToText

Background

In ISO 8601, a duration is the amount of intervening time in a time interval, written in a form such as P3Y6M4DT12H30M5S.[1] The ISO 8601 duration format, or a subset of it, is widely used independent of time intervals, as in the Java 8 Duration class.[1] In DuckDB, an INTERVAL is built from amounts together with units, so INTERVAL '1 month 1 day' is stored as 1 month and 1 day.[2]

When to use it

In DuckDB, an INTERVAL keeps months, days and microseconds as three separate basis units, because a month has no fixed number of days and a day can be 23 or 25 hours long under daylight saving time.[2] For equality and ordering only, DuckDB counts a month as 30 days, so INTERVAL 30 DAYS equals INTERVAL 1 MONTH, although adding each to DATE '2020-01-01' gives different results.[2] Keep a duration in its parts while it will be added to dates, and turn it into one number of seconds only when you need to total or rank lengths of time. (Meridian's view)

In DuckDB, a decimal amount used with a unit keyword is truncated to an integer unless the unit is seconds or milliseconds, so INTERVAL '1.5' YEARS gives 12 months.[2] Write fractional durations in a smaller unit, such as 18 months rather than 1.5 years, so no part of the amount is silently dropped. (Meridian's view)

The method

In DuckDB, datepart splits an interval's months component into years and months and its microseconds component into hours, minutes and microseconds, but leaves the days component whole.[2] DuckDB's epoch function returns an interval's total number of seconds as a double precision floating point number, so epoch(INTERVAL 5 HOUR) is 18000.0.[3] DuckDB's normalized_interval carries units that overflow their range into the next larger unit, turning INTERVAL '30 days' into INTERVAL 1 MONTH.[3]

PostgreSQL's justify_hours converts 24-hour periods to days, and justify_days converts 30-day periods to months.[4]

Compared with the alternatives

In ISO 8601, a time interval is the intervening time between two time points, and its amount is expressed by a duration.[1]

Sources

  1. [1]ISO 8601 - Wikipediasecondary source
  2. [2]Interval Type – DuckDBdocumentation
  3. [3]Interval Functions – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operatorsdocumentation