Re: [HACKERS] Date conversion using day of week

On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> 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.

Just to play Devils advocate here, but why not? The day name is the same either
way, it is the index that changes. I am not sure why that could not be context
specific?

>
> Cheers,
> BJ

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com