MeridianMERIDIAN

ISO 8601 Duration

datetime.duration.iso_8601was datetime.duration in v1

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.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs INTERVAL
PT30M00:30:00
PT1H30M01:30:00
P1DT12H1 day 12:00:00
P1Y6M1 year 6 months
P2W14 days
P1Y2M3DT4H5M6S1 year 2 months 3 days 04:05:06
Standard

Shape

Pattern
^-?P(?=\d|T\d)(?:(\d+)Y)?(?:(\d+)M)?(?:(\d+)[DW])?(?:T(?:(\d+)H)?(?:(\d+)M)?(?:(\d+(?:\.\d+)?)S)?)?$
ValueLengthShape
PT30M5matches
PT1H30M7matches
P1DT12H7matches
P1Y6M5matches
P2W3matches
P1Y2M3DT4H5M6S14matches
PT304rejected: 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. [1]ISO 8601secondary source
  2. [2]XML Schema Part 2: Datatypes Second Editionstandard
  3. [3]Table Schemastandard
  4. [4]RFC 5545: Internet Calendaring and Scheduling Core Object Specification (iCalendar)standard
  5. [5]Duration - Schema.org Typestandard
  6. [6]Videos | YouTube Data API | Google for Developersdocumentation
  7. [7]Duration (Java SE 21 & JDK 21)documentation

Detection notes

v1 name: datetime.duration. 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.

Use it

CLI · DuckDB
$ finetype infer -i "PT30M" --mode column
→ datetime.duration.iso_8601

SELECT ft_infer('PT30M');
-- → 'datetime.duration.iso_8601'
Cast expression
CAST(
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)Y', 1), '') || ' years ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, 'P[^T]*?(\d+)M', 1), '') || ' months ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)W', 1), '') || ' weeks ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)D', 1), '') || ' days ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)H', 1), '') || ' hours ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, 'T.*?(\d+)M', 1), '') || ' minutes ', '') ||
  COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+(?:\.\d+)?)S', 1), '') || ' seconds', '0 seconds')
AS INTERVAL)

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

Type Registry