ISO 8601 Duration

datetime.duration.iso_8601

Duration in ISO 8601 format (PnYnMnDTnHnMnS or PnW for weeks). Used in iCalendar, XML Schema, YouTube API, Google Calendar, and many APIs. Covers both compact (PT30M) and verbose (P1Y2M3DT4H5M6S) forms, a decimal fraction (full stop or comma) on the smallest component (PT0,5S), and the alternative format PYYYY-MM-DDThh:mm:ss.

Use it

Shell

finetype infer -i 'PT30M' --mode column
# → datetime.duration.iso_8601

DuckDB

SELECT ft_infer('PT30M');
-- → 'datetime.duration.iso_8601'

Cast template: {col} is your column

CAST(CASE WHEN REGEXP_MATCHES({col}, '^P\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}$') THEN
  REGEXP_EXTRACT({col}, '^P(\d{4})-', 1) || ' years ' ||
  REGEXP_EXTRACT({col}, '^P\d{4}-(\d{2})-', 1) || ' months ' ||
  REGEXP_EXTRACT({col}, '^P\d{4}-\d{2}-(\d{2})T', 1) || ' days ' ||
  REGEXP_EXTRACT({col}, 'T(\d{2}:\d{2}:\d{2})$', 1)
ELSE
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)Y', 1), ',', '.'), '') || ' years ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '^-?P(?:[\d.,]+Y)?([\d.,]+)M', 1), ',', '.'), '') || ' months ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)W', 1), ',', '.'), '') || ' weeks ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)D', 1), ',', '.'), '') || ' days ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)H', 1), ',', '.'), '') || ' hours ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, 'T(?:[\d.,]+H)?([\d.,]+)M', 1), ',', '.'), '') || ' minutes ', '') ||
  COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)S', 1), ',', '.'), '') || ' seconds', '0 seconds')
END AS INTERVAL)

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

Value	As `INTERVAL`
PT30M	00:30:00
PT1H30M	01:30:00
P1DT12H	1 day 12:00:00
P1Y6M	1 year 6 months
P2W	14 days
P1Y2M3DT4H5M6S	1 year 2 months 3 days 04:05:06
PT0.5S	00:00:00.5
PT1H30,5M	01:30:30
P0,5Y	6 months
P0003-06-04T12:30:05	3 years 6 months 4 days 12:30:05

Standard

https://www.iso.org/standard/70907.html

Pattern

Regular expression

^(?:-?P(?=\d|T\d)(?!.*[.,]\d+[A-Z]T?\d)(?:(\d+(?:[.,]\d+)?)Y)?(?:(\d+(?:[.,]\d+)?)M)?(?:(\d+(?:[.,]\d+)?)[DW])?(?:T(?:(\d+(?:[.,]\d+)?)H)?(?:(\d+(?:[.,]\d+)?)M)?(?:(\d+(?:[.,]\d+)?)S)?)?|P\d{4}-(?:0\d|1[0-2])-(?:[0-2]\d|30)T(?:[01]\d|2[0-4]):[0-5]\d:[0-5]\d)$

Value	Length	Shape
PT30M	5	matches
PT1H30M	7	matches
P1DT12H	7	matches
P1Y6M	5	matches
P2W	3	matches
P1Y2M3DT4H5M6S	14	matches
PT0.5S	6	matches
PT1H30,5M	9	matches
P0,5Y	5	matches
P0003-06-04T12:30:05	20	matches
PT30	4	rejected: one character short

Background

Origin

ISO 8601 is maintained by the International Organization for Standardization.^[1] The first edition of ISO 8601 was published as ISO 8601:1988 in June 1988.^[1] The fourth revision of ISO 8601 was issued in February 2019 as two parts, ISO 8601-1:2019 and ISO 8601-2:2019.^[1]

ISO 8601 durations define the amount of intervening time in a time interval.^[1] An ISO 8601 duration is written in the pattern PnYnMnDTnHnMnS, or PnW for a number of weeks.^[1] In an ISO 8601 duration, P is the duration designator (for period) placed at the start of the representation.^[1] The letter T is the time designator that precedes the time components of an ISO 8601 duration.^[1] ISO 8601 settles the double use of the letter M by position: P1M is a one-month duration and PT1M is a one-minute duration.^[1] An ISO 8601 duration must contain at least one element, so a bare P is not a valid representation of zero seconds.^[1] ISO 8601 lets values exceed their usual carry-over points, so PT36H and P1DT12H are both allowed.^[1]

Use

XML Schema Part 2: Datatypes gives its duration datatype the ISO 8601 extended format PnYnMnDTnHnMnS as its lexical representation.^[2] XML Schema's duration datatype allows an optional preceding minus sign to indicate a negative duration.^[2] The Frictionless Data Table Schema follows the XML Schema definition of the duration datatype for its own duration field type.^[3] iCalendar's duration value type, defined in RFC 5545, is based on the ISO 8601 complete representation basic format with designators.^[4] Unlike ISO 8601, RFC 5545's duration value type does not support the Y and M designators for years and months.^[4] RFC 5545 says negative durations are typically used to schedule an alarm to trigger before an associated time.^[4] Schema.org's Duration type asks for values in ISO 8601 duration format.^[5]

The YouTube Data API gives a video's length in contentDetails.duration as an ISO 8601 duration, such as PT15M33S.^[6] Java's Duration.parse accepts formats based on the ISO 8601 duration format PnDTnHnMn.nS, with days considered to be exactly 24 hours.^[7] Java's Duration.toString does not output multiples of 24 hours as days, so two days is written PT48H.^[7]

A duration in months has no fixed length on its own: P1M could be 28, 29, 30 or 31 days.^[1] PT36H is not the same as P1DT12H when the interval crosses a switch to or from daylight saving time.^[1] XML Schema gives durations only a partial order, because there is no determinate relationship between one month (P1M) and 30 days (P30D).^[2] Turn a duration into a fixed number of seconds only when it holds no years, months or days, or when you know the date it starts from. (Meridian's view) Test a few negative values from the source before assuming a single leading minus sign is the only way a negative duration is written. (Meridian's view)

Sources

[1]ISO 8601secondary source
[2]XML Schema Part 2: Datatypes Second Editionstandard
[3]Table Schemastandard
[4]RFC 5545: Internet Calendaring and Scheduling Core Object Specification (iCalendar)standard
[5]Duration - Schema.org Typestandard
[6]Videos | YouTube Data API | Google for Developersdocumentation
[7]Duration (Java SE 21 & JDK 21)documentation

How it's detected

Updated to cover full ISO 8601 duration spec including weeks (PnW), negative durations (-P...), and fractional seconds (PT1.5S). Alias iso_8601_verbose added — no separate type needed since same regex covers both compact and verbose forms. ISO 8601-1 allows a decimal fraction (comma preferred, full stop accepted) on the smallest component only, so P0.5Y1M is rejected. The alternative format PYYYY-MM-DDThh:mm:ss (months up to 12, days up to 30, hours up to 24) is accepted and the transform reads it as years, months, days and a time of day. A negative duration (-P...) is accepted but the transform does not carry the sign. A fractional year or month converts at DuckDB's interval rates (a month is 30 days); a W component converts to 7 days each. ISO 8601-1 also allows 60 as the minute or second of the alternative format; that conforms to the standard but is rejected here, because the transform cannot cast it.

Schema

https://meridian.online/schemas/datetime.duration.iso_8601 · Frictionless type duration

finetype taxonomy datetime.duration.iso_8601 -o json-schema

Aliases

durationiso_8601_verbose