Re: [HACKERS] Date conversion using day of week

On 1 April 2011 04:16, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> This whole discussion opens a #10 sized can o' worms. Admittedly, I don't
> have good knowledge of any SQL-mandated interpretations of an ISO date - but
> based on my reading of ISO formatting I see the following issues:
>
> 1. What we describe in the documentation as an ISO date is actually an ISO
> *week* date - a special purpose format included within ISO8601. 2011-03-31
> is also an ISO date as are 20110331, 20110331T013212 and
> 20110331T21.3344298. "Fixing" this is probably as simple as a clarification
> in the documentation.

In the docs paragraph I quoted upthread, the full name "ISO week date"
is given. Elsewhere the shorthand "ISO" or "ISO date" is used, in
contrast to the ordinary Gregorian style. This is the only sense in
which we refer to "ISO" in the context of to_date, but I have no real
objection to expanding this to the full name "ISO week date"
everywhere it is mentioned, if people find the current usage
ambiguous.

> 2. The ISO week-date format is defined as having the week-number prefaced by
> a "W" as in 2011-W03-7.
...
> However PostgreSQL does *not* accept that as input even as
> specified as an "ISO" date:

It does, but you must use the somewhat awkward quoting notation to
indicate that the W is a literal character in the input string, not a
formatting character: 'IYYY-"W"IW-ID'

...
> What I've concluded is that the root of the entire problem is providing ISO
> formatting options in pieces at all. The ISO date format has various
> requirements like ordering from largest temporal term to smallest,
> zero-padding, ""W" prefacing an ISO week, no "skipping" of temporal terms
> (201105 is May 2011, never the 5th of an unknown month), etc. all intended
> to make an ISO date string unambiguous. As such, it should only require a
> single format option saying "this is an ISO8601 date string" and mixing of
> ISO and Gregorian date formatting becomes impossible.

I agree with your summary of the ISO standards. Unfortunately,
to_date and its cohorts are not targeting ISO. They are targeting
quasi-compatibility with some Oracle functions of the same name, I
suppose to make life easier for folks who are migrating from Oracle to
Postgres. Any proposed reform of these (admittedly weird and kludgy)
functions is viewed through that lens, and usually rejected on those
grounds. I've been down that road before. There's not much point
having compatibility functions if they aren't, well, compatible.

In the big picture, to_date isn't meant to be the general entry point
for parsing dates. If you wanted to make ISO8601 work as a syntax for
inputting date type literals vis. SELECT date '2011-W14-01', you might
have a better shot at getting that off the ground.

Cheers,
BJ