Re: [HACKERS] Date conversion using day of week

On 31 March 2011 03:15, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> On 03/29/2011 04:24 PM, Adrian Klaver wrote:
>> ...
>> Well the strange part is only fails for SUN:...
>> test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
>>   to_date
>> ------------
>>  2011-03-28
>> ...
>
> You specified Sunday as the day but the date returned is a Monday. I would
> categorize that as a bug. (Hackers cc'd). Since Sunday is the last day of an
> ISO week, it should have returned 2011-04-03.
>
> My first inclination without consulting source or morning coffee is that
> PostgreSQL is seeing Sunday as day zero. Note that while:

The relevant paragraphs in the docs are:

--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:

* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.

Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a "month" or "day of month" has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--

We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.

With the attached patch applied, this is what happens instead:

# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.

If we wanted to make it "work", then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.

Cheers,
BJ