Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'

Mark Lorenz <postgres(at)four-two(dot)de> writes:
>> Why not? These format codes are specified as
>> D day of the week, Sunday (1) to Saturday (7)
>> WW week number of year (1–53) (the first week starts on the first day
>> of the year)

> I don't want to create any connection here. The day is calculated
> correctly. But the week number is wrong. 1997-02-03 was in week number
> 6, as well as 1997-02-04. But Postgres returns 5.

The week number is only wrong if you persist in ignoring the very clear
definition given in the manual. According to the stated definition of WW,
"week 1" consists of Jan 1 to Jan 7, "week 2" to Jan 8-14, etc. So it's
correct for both of those dates to be in "week 5". There are other
possible definitions of "week" of course, such as the ISO week, under
which both those dates would be in week 6 (of 1997 anyway, not all other
years). But if you want ISO week you should ask for it with "IW", not
expect that we'll change the longstanding behavior of "WW" to match.

As far as I can see, the only way to make a week definition that
gives sensible results in combination with "D" is to do something
like what ISO does, but with Sunday as the start day instead of Monday.
But having three different week definitions seems more likely to
confuse people (even more) than to be helpful. Plus you'd also need
analogs of IYYY, IDDD, etc.

Why not just use IYYY-IW-ID, instead? You'd have to adapt to
week-starts-on-Monday, but you'd be using a notation that a lot
of people are already familiar with, instead of inventing your own.

Another possibility, perhaps, is to use WW in combination with
some new field that counts 1-7, 1-7, 1-7, ... starting on Jan 1.
But then that wouldn't have any easy mapping to day names, so
there's no free lunch.

Throwing MM into the mix makes it even more exciting, as month
boundaries don't correspond with week boundaries either. I don't
see any rational way to make YYYY-MM-W or YYYY-MM-W-D patterns
that behave in a numerically consistent fashion. (Note that ISO
didn't try --- there is no "ISO month".)

The bottom line is that these various definitions aren't mutually
consistent, and that's just a fact of life, not something that can
be fixed.

In any case, backwards compatibility alone would be a sufficient
reason to reject a patch that changes the established behavior
of the existing format codes. Whether you think they're buggy or
not, other people are relying on the existing documented behavior.

Perhaps we'd consider a patch that adds some new format codes with
new behavior. But personally I'd vote against implementing new
format codes unless you can point to well-established standards
supporting their definitions. to_char/to_date are impossibly
complex and unmaintainable already; we don't need to add more
features with narrow use-cases to them.

regards, tom lane