Thread: The IYYY mess again
In bug #12367 http://www.postgresql.org/message-id/20141229031218.8013.51171@wrigleys.postgresql.org we see yet another iteration of somebody trying to combine to_char's IYYY specifier with regular Gregorian MM/DD fields. It occurs to me that this is largely our own fault, because the fine manual just defines IYYY as "ISO year". I'm sure the typical newbie thought process is "that sounds like a standard year, I'll use that". There is a warning against combining IYYY with MM/DD, but it's buried in trivia far down the page. I did a bit of googling and came across http://en.wikipedia.org/wiki/ISO_week_date in which this construct is called an "ISO week-numbering year". Not having a copy of ISO 8601, I'm not sure if that's the standard's terminology; but ISTM that if we consistently referred to the Ixxx format specifiers as "ISO week-numbering foo" then this type of error might become a little less attractive. Objections, better ideas? regards, tom lane
On Dec 29, 2014, at 7:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > In bug #12367 > http://www.postgresql.org/message-id/20141229031218.8013.51171@wrigleys.postgresql.org > we see yet another iteration of somebody trying to combine to_char's > IYYY specifier with regular Gregorian MM/DD fields. > > It occurs to me that this is largely our own fault, because the fine > manual just defines IYYY as "ISO year". I'm sure the typical newbie > thought process is "that sounds like a standard year, I'll use that". > There is a warning against combining IYYY with MM/DD, but it's buried > in trivia far down the page. > > I did a bit of googling and came across > http://en.wikipedia.org/wiki/ISO_week_date > in which this construct is called an "ISO week-numbering year". > > Not having a copy of ISO 8601, I'm not sure if that's the standard's > terminology; but ISTM that if we consistently referred to the Ixxx > format specifiers as "ISO week-numbering foo" then this type of error > might become a little less attractive. > > Objections, better ideas? I've seen this problem a few times on IRC too. An explicit warning / cross reference on IYYY that the user almost certainly wants YYYY instead might be even better. Cheers, Steve
Tom Lane-2 wrote > In bug #12367 > http://www.postgresql.org/message-id/ > 20141229031218.8013.51171@.postgresql > > There is a warning against combining IYYY with MM/DD, but it's buried > in trivia far down the page. Can we move this warning/notice into code? Basically warn/disallow IYYY-MM-DD (and similar) as a valid format? And why not make it an error. If someone really needed to output mixed modes they would be able to concatenate the two halves together to get the desired result so it isn't like we are making it impossible to accomplish - but the typical mistake goes away. David J. -- View this message in context: http://postgresql.nabble.com/The-IYYY-mess-again-tp5832272p5832322.html Sent from the PostgreSQL - docs mailing list archive at Nabble.com.
David G Johnston <david.g.johnston@gmail.com> writes:
> Tom Lane-2 wrote
>> There is a warning against combining IYYY with MM/DD, but it's buried
>> in trivia far down the page.
> Can we move this warning/notice into code? Basically warn/disallow
> IYYY-MM-DD (and similar) as a valid format?
> And why not make it an error. If someone really needed to output mixed
> modes they would be able to concatenate the two halves together to get the
> desired result so it isn't like we are making it impossible to accomplish -
> but the typical mistake goes away.
I would say no, and no. It's not at all unreasonable to want to print
"YYYY-MM-DD (IYYY-IDDD)" or something like that. And even if we think
it's dumb, it's not to_char()'s place to tell people they can't print
silly combinations. One could argue in fact that the only reason for
to_char() to live at all is to print "silly" combinations --- otherwise,
you might as well just cast your timestamp to text.
regards, tom lane
David G Johnston <david.g.johnston@gmail.com> writes:
> Tom Lane-2 wrote
>> There is a warning against combining IYYY with MM/DD, but it's buried
>> in trivia far down the page.
> Can we move this warning/notice into code? Basically warn/disallow
> IYYY-MM-DD (and similar) as a valid format?
> And why not make it an error. If someone really needed to output mixed
> modes they would be able to concatenate the two halves together to get the
> desired result so it isn't like we are making it impossible to accomplish -
> but the typical mistake goes away.
I would say no, and no. It's not at all unreasonable to want to print
"YYYY-MM-DD (IYYY-IDDD)" or something like that. And even if we think
it's dumb, it's not to_char()'s place to tell people they can't print
silly combinations. One could argue in fact that the only reason for
to_char() to live at all is to print "silly" combinations --- otherwise,
you might as well just cast your timestamp to text.
I didn't suggest "YYYY-MM-DD (IYYY-IDDD)" should throw an error...we can be more conservative/targeted than that.
Anyway, I get your point though you know as well as I do that documentation isn't always the answer. The OP on this issue likely figured he was using the correct format because up until today it was returning the correct result. Its easy enough in hindsight to now look at the documentation and see why he was wrong but it doesn't fix the fact that the code now needs to be fixed when a format specification error would have prevented this simple misunderstanding - strictly using IYYYY-MM-DD instead of YYYY-MM-DD.
Anyway, on the documentation side splitting up the single large table into separate "calendar" tables would make the distinction clearer and also group like specifications together. I would go so far as to repeat those items that can be used with more than one calendar and so make each table self-sufficient. The wording change may further help but breaking out and describing the nuances of the different calendar interpretations of a given point-in-time would add a visual separation for the reader.
David J.
On Mon, Dec 29, 2014 at 10:06:09AM -0500, Tom Lane wrote: > In bug #12367 > http://www.postgresql.org/message-id/20141229031218.8013.51171@wrigleys.postgresql.org > we see yet another iteration of somebody trying to combine to_char's > IYYY specifier with regular Gregorian MM/DD fields. > > It occurs to me that this is largely our own fault, because the fine > manual just defines IYYY as "ISO year". I'm sure the typical newbie > thought process is "that sounds like a standard year, I'll use that". > There is a warning against combining IYYY with MM/DD, but it's buried > in trivia far down the page. > > I did a bit of googling and came across > http://en.wikipedia.org/wiki/ISO_week_date > in which this construct is called an "ISO week-numbering year". > > Not having a copy of ISO 8601, I'm not sure if that's the standard's > terminology; but ISTM that if we consistently referred to the Ixxx > format specifiers as "ISO week-numbering foo" then this type of error > might become a little less attractive. > > Objections, better ideas? +1 for saying "ISO week-numbering year". -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +