Re: to_date() and to_timestamp() with negative years

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

adrian.klaver@xxxxxxxxxxx wrote:

Not sure how this can be handled in a totally predictable way given the unpredictable ways in which datetime strings are formatted?

The only thing I can say it is it points out that when working with datetimes settling on a standard format is your best defense against unpredictable results.

Thank you very much, again, for your help with my seemingly endless nitpicking questions on this matter, Adrian. Here's the most favorable conclusion that I can draw:

1. A precedent has been set by The SQL Standard folks together with the PostgreSQL implementation and other implementations like Oracle Database. All this stretches back a long time—to more than four decades ago.

2. This has brought us in PG to the rules that "Table 9.26. Template Patterns for Date/Time Formatting", "Table 9.27. Template Pattern Modifiers for Date/Time Formatting", and "Usage notes for date/time formatting" set out and interpret.

3. The rules are hard to understand and the PG doc gives insufficient detail to allow the outcomes in corner cases like you just showed us to be predicted confidently. Some users seek to understand the rules by reading PG's source code.

4. Certainly, the rules set a significant parsing challenge. You hint that they might even prevent a complete practical solution to be implemented.

5. None of this matters when the app designer has the freedom to define how date-time values will be provided, as text values, by user interfaces or external systems. In these cases, the complexity can be controlled by edict and correct solutions can be painlessly implemented and tested. Not a day goes by that I don't have to enter a date value at a UI. And in every case, a UI gadget constrains my input and makes its individual fields available to the programmer without the need for parsing—so there's reason to be optimistic. The programmer can easily build the text representation of the date-time value to conform to the simple rules that the overall application design specified.

6. In some cases text data that's to be converted arrives in a format that cannot be influenced. And it might contain locutions like we've been discussing ("zero" meaning "1 BC", unfavorable paradigms for separating tokens, and the like). In these cases, the diligent programmer might, just, be able to use the full arsenal of available tools to implement a scheme that faultlessly parses the input. But the likelihood of bugs seems to be pretty big.

I'll say "case closed, now" — from my side, at least.