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');
> 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
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
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.
In response to
pgsql-hackers by date
|Next:||From: Heikki Linnakangas||Date: 2011-03-31 05:35:08|
|Subject: Re: Re: [COMMITTERS] pgsql: Fix plpgsql to release SPI
plans when a function or DO block is|
|Previous:||From: Fujii Masao||Date: 2011-03-31 02:46:54|
|Subject: Re: Replication server timeout patch|
pgsql-general by date
|Next:||From: Leonardo Francalanci||Date: 2011-03-31 07:57:09|
|Subject: read-only UNLOGGED tables|
|Previous:||From: Joshua D. Drake||Date: 2011-03-30 23:17:19|
|Subject: Re: pg_restore|