I'm quoting here from "Usage notes for date/time formatting" just below "Table 9.25. Template Pattern Modifiers for Date/Time Formatting" here:
https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE on the page "9.8. Data Type Formatting Functions". Find this: « In to_timestamp and to_date, negative years are treated as signifying BC. If you write both a negative year and an explicit BC field, you get AD again. An input of year zero is treated as 1 BC. » This seems to be a suspect solution looking for a problem for these reasons: 1. Nobody ever talks about dates by saying "the year minus 42". It's always "42 BC". Nor do they talk about "year zero" in the Gregorian calendar 'cos there's no such year. 2. If you invoke "make_timestamp()" or "make_timestamptz()" with a negative (or zero) argument for "year", then you get the "22008" error. 3. The text-to-date typecast « '-2021-06-15'::date » gets the "22009" error, as it does with "0000". 4. The unary "minus" operator is shorthand for "subtract the operand from zero" — i.e. "-x" means "0 - x". But there is no year zero. And anyway, the difference between two "date" values is an "integer" value; and the difference between two "timestamp[tz]" values is an "interval" value. Finally, the implementation seems to be buggy (as I observed it in PG 13.4 on my MacBook with up-to-date macOS). Try this: \x on select to_date( '15/06/-2021', 'DD/MM/YYYY' ) as a1, to_date( '15/06/-2021 BC', 'DD/MM/YYYY/AD' ) as a2, '' as "-", to_date( '15 06 -2021', 'DD MM YYYY' ) as b1, to_date( '15 06 -2021 BC', 'DD MM YYYY AD' ) as b2; \x off Notice that the difference between the first two expressions (that produce the values "a1" and "a2") and the second two expressions (that produce the values "b1" and "b2") is how the to-be-converted substrings for "DD", "MM", and "YYYY" are separated. Otherwise, they express the same intention. So "b1" should be the same as "a1" and "b2" should be the same as "a2". This is the result: a3 | 2021-06-15 BC a4 | 2021-06-15 - | b3 | 2021-06-15 b4 | 2021-06-15 BC The "a" values are right (by the rule in the PG doc)—and the "b" values are wrong. If you express the same intention without the complication of using "-2021", then you get the right results throughout (and for any other way that you care to separate the to-be-interpreted substrings). Moreover, I have an old PG 11.9 in a Ubuntu VM. The same test gets these results: a1 | 2022-06-15 BC a2 | 2022-06-15 BC - | b1 | 2022-06-15 BC b2 | 2022-06-15 BC Also buggy. But in a different way. And moreover (again) — I tride the test in our current latest YugabyteDB. This uses the PG 11.2 C code as is. (You'll have to trust me when I say that the test executes entirely here and goes nowhere near our own code,) This is the result (again on my MacBook). a1 | 2022-06-15 BC a2 | 2022-06-15 BC - | b1 | 2021-06-15 b2 | 2021-06-15 BC Also buggy. But in YET a different way. It looks like you have a tranche of baroque C code that wrongly implements a misguided idea. And that it's been tinkered around with between at least a couple of release boundaries without proper testing. Is there any chance that you might be bold and simply make negative "year" values illegal in "to_date()" and "to_timestamp()" — just as they already are in "make_timestamp()", "make_timestamptz()", and the "from text" typecasts to date-time moment values?
