Hello, I maintain a PostgreSQL driver and have been mapping date values between it and Kotlin, where the standard library emits ISO 8601 with the expanded year representation for years outside 0001..9999. That is where I ran into something I would like to understand before I write it down as intended behaviour.
The documentation describes DateStyle 'ISO' as ISO 8601. ISO 8601 represents years outside 0001..9999 with an explicit sign and more than four digits (+10000, -0001). PostgreSQL accepts neither form on input, although it holds and prints the same values happily. PostgreSQL 18.4, DateStyle = 'ISO, DMY', lc_messages = 'C': -- values PostgreSQL holds and prints SELECT '5874897-12-31'::date; -- 5874897-12-31 SELECT '10000-01-02'::date; -- 10000-01-02 SELECT '0002-01-02 BC'::date; -- 0002-01-02 BC -- the same values, spelled the way ISO 8601 spells them SELECT '+5874897-12-31'::date; ERROR: time zone displacement out of range: "+5874897-12-31" SELECT '+10000-01-02'::date; ERROR: time zone displacement out of range: "+10000-01-02" SELECT '-0001-01-02'::date; ERROR: invalid input syntax for type date: "-0001-01-02" SELECT '0000-01-02'::date; ERROR: date/time field value out of range: "0000-01-02" -- and the same on timestamptz SELECT '+10000-01-02 00:00:00Z'::timestamptz; ERROR: time zone displacement out of range: "+10000-01-02 00:00:00Z" Two separate things seem to be going on, and I am unsure whether either is considered a defect. The first is the leading sign. 5874897-12-31 is inside what a date holds - PostgreSQL stores it and prints it back - but the ISO spelling of the same value is refused, and the error suggests why: the '+' appears to be read as the start of a time zone displacement rather than as the sign of an expanded year. So this is not a range limitation, it is the parser reaching a different conclusion about what the character means. The second is the year numbering. ISO 8601 counts through a year zero, so ISO year 0000 is 1 BC and ISO -0001 is 2 BC, where PostgreSQL counts BC from one and has no year zero at all. While mapping between the two is mathematically straightforward, the parser currently does not translate these negative ISO representations into PostgreSQL's BC equivalents. My question is really about the documentation rather than the code. If expanded years are out of scope - which is entirely reasonable, given the sign clashes with time zone syntax and the year-zero difference is real - would it be worth saying so, along the lines of "ISO 8601 for years 0001..9999"? As written, the claim reads as full ISO 8601 support, and a client library that emits conformant ISO 8601 for a date PostgreSQL can hold gets an error which does not suggest what actually went wrong. I am happy to be told this is known and deliberate; I could not find it stated and wanted to check before treating it as such. Thanks, Kacper Kuras
