> [email protected] 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.
