MeridianMERIDIAN

ISO Date

datetime.date.isowas datetime.date in v1

ISO 8601 date format. The universal default for date representation in databases, APIs, and data interchange.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs DATE
2024-01-152024-01-15
2019-12-312019-12-31
2000-01-012000-01-01
%Y
four-digit year
%m
month, two digits
%d
day of month, two digits

Written by

CLDR 46 via finetype 0.6.64

Each locale below writes this format with its CLDR date pattern at the styles named beside it.

aa short · aa (DJ, ER) short · ab short · af short · af (NA) short · an short · ann short · apc short · arn short · ba short · bal short · bal (Arab) short · bew short · blt short · bm (Nkoo) short · bo short · bo (IN) short · bss short · byn short · cad short · cch short · ce short · cho short · cic short · co short · csw short · cu short · dv short · en (CA, Dsrt, SE, Shaw) short · fr (CA) short · frr short · gaa short · gez short · gez (ER) short · gn short · gv short · ha (Arab, Arab-SD) short · ii short · io short · iu (Latn) short · jbo short · jgo short · kaa short · kaa (Cyrl, Latn) short · kaj short · kcg short · ken short · kk (Arab) short · kl short · kpe short · kpe (GN) short · kw short · la short · lij short · lmo short · lt short, medium · ltg short · mdf short · mg short · mgo short · mhn short · mic short · mn (Mong) short · moh short · mus short · myv short · nds short · nds (NL) short · nr short · nso short · nv short · ny short · oc short · oc (ES) short · osa short · pap short · pap (AW) short · pis short · prg short · quc short · rhg short · rhg (Rohg, Rohg-BD) short · rif short · rw short · scn short · se short · se (SE) short · shn short · shn (TH) short · si short · sid short · skr short · sma short · sma (NO) short · smj short · smj (NO) short · sms short · sn short · ss short · ss (SZ) short · ssy short · st short · st (LS) short · sv short · sv (AX, FI) short · szl short · tig short · tn short · tn (BW) short · tok short · tpi short · trv short · trw short · ts short · tyv short · ug short · und short · ve short · vmw short · vo short · wa short · wae short · wal short · wbp short · za short

  • Python date.isoformat()
  • DuckDB CAST(date AS VARCHAR)
Standard

Shape

Pattern
^\d{4}-\d{2}-\d{2}$
ValueLengthShape
2024-01-1510matches
2019-12-3110matches
2000-01-0110matches
2024-01-19rejected: one character short

Background

Origin

ISO 8601 is maintained by the International Organization for Standardization.[1] ISO Technical Committee TC 154 has direct responsibility for the ISO 8601 standards.[1] ISO 8601 offers a way to avoid misinterpreting numeric dates and times when data moves between countries with different conventions for writing them.[1] The first edition of ISO 8601 was published as ISO 8601:1988 in June 1988.[1] ISO 8601:1988 unified and replaced a number of older ISO standards on aspects of date and time notation.[1] A second edition, ISO 8601:2000, superseded the first in 2000.[1] A third edition, ISO 8601:2004, was published on 1 December 2004.[1] ISO 8601:2004 was replaced by ISO 8601-1:2019 and ISO 8601-2:2019 on 25 February 2019.[1] An amendment to ISO 8601-1 was published in October 2022.[1]

In an ISO 8601 calendar date, YYYY is a four-digit year, MM a two-digit month from 01 to 12, and DD a two-digit day from 01 to 31.[1] ISO 8601 allows both the YYYY-MM-DD and the YYYYMMDD forms for a complete calendar date.[1] Wikipedia's example is 5 April 1981, written 1981-04-05 in the extended format or 19810405 in the basic format.[1] ISO 8601 orders date and time values from the largest unit of time to the smallest.[1] ISO 8601 uses the Gregorian calendar.[1] ISO 8601 says the years 0000 through 1582 shall only be used by mutual agreement of the partners in information interchange.[1]

The W3C note Date and Time Formats was written by Misha Wolf and Charles Wicksteed.[2] Date and Time Formats was submitted to W3C on 15 September 1997.[2] Date and Time Formats defines a profile of ISO 8601.[2] The W3C note's complete date form is YYYY-MM-DD, with the example 1997-07-16.[2] RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.[3] RFC 3339 defines full-date as a four-digit year, a two-digit month and a two-digit day joined by hyphens.[3] RFC 3339 says Internet protocols MUST generate four digit years in dates.[3]

Use

Python's date.isoformat() returns a string representing the date in ISO 8601 format, YYYY-MM-DD.[4] Before Python 3.11, date.fromisoformat() only supported the format YYYY-MM-DD.[4] DuckDB requires date literals to be formatted according to the ISO 8601 format, YYYY-MM-DD.[5] DuckDB also accepts the strings epoch, infinity and -infinity as date input.[5] PostgreSQL's default output format for its date and time types is ISO.[6] PostgreSQL's documentation notes that the SQL standard requires the use of the ISO 8601 format.[6] PostgreSQL lists 1999-01-08 as ISO 8601 and its recommended date input format, read as January 8 in any DateStyle mode.[6] The Frictionless Table Schema specification says a date field in the default format MUST be in ISO8601 format YYYY-MM-DD.[7]

A column that already reads 2024-01-15 is the easy case, but check that every value is a real calendar date rather than ten characters in the right shape before you trust it. (Meridian's view) If a column mixes the hyphenated and compact forms, normalise them to one before comparing or sorting the values as text. (Meridian's view) Where you choose the output format, writing dates this way spares the next reader from guessing which number is the day and which the month. (Meridian's view)

Where you'll meet it

The UK government's standard tells government systems to use ISO 8601:2004 to represent dates and times in a machine-readable format.[8] The UK government's standard covers APIs, data transferred between systems and date sorting, such as dates at the start of filenames.[8]

Sources

  1. [1]ISO 8601 - Wikipediasecondary source
  2. [2]Date and Time Formatsstandard
  3. [3]RFC 3339: Date and Time on the Internet: Timestampsstandard
  4. [4]datetime — Basic date and time typesdocumentation
  5. [5]Date Typesdocumentation
  6. [6]8.5. Date/Time Typesdocumentation
  7. [7]Table Schemastandard
  8. [8]Formatting dates and times in dataorganisation

Detection notes

v1 name: datetime.date

In Meridian's open data

Use it

CLI · DuckDB
$ finetype infer -i "2024-01-15" --mode column
→ datetime.date.iso

SELECT ft_infer('2024-01-15');
-- → 'datetime.date.iso'
Cast expression
strptime({col}, '%Y-%m-%d')::DATE
-- Format: %Y-%m-%d

Schema

https://meridian.online/schemas/datetime.date.iso · Frictionless type date · %Y-%m-%d

finetype taxonomy datetime.date.iso -o json-schema

Type Registry