Re: ISO week dates - Mailing list pgsql-general

On 11/24/06, Alban Hertroys <alban@magproductions.nl> wrote:
> Brendan Jurd wrote:
> > On 11/23/06, Alban Hertroys <alban@magproductions.nl> wrote:
> >> Just verifying, but aren't both formats ISO? In that case maybe it'd be
> >> better to have a (per database) setting that specifies which one?
> >
> > The term "ISO" is broad and perhaps a little misleading.  ISO 8601
> > specifies many date and time formats, of which the "week date" is one.
> > The field I have tentatively named "isoyear" refers to the year,
> > according to the ISO week date calendar, which is similar to, but
> > distinct from, the Gregorian calendar.
>
> Ah, now I remember; the distinction is "ISO 8601" vs. "Gregorian". You'd
> think there'd be an ISO spec describing the gregorian calendar too.

You got the wrong idea.  ISO 8601 describes various formats that can
be used to describe dates and times, including:
 * Gregorian formats with year, month and day-of-month,
 * a "week date" format with year, week, and day-of-week, and
 * an "ordinal date" format with the year and day-of-year.

The term "ISO date" ambiguously refers to a date which conforms to any
of the above formats.

>
> > ISO 8601 provides for dates expressed in the Gregorian style and the
> > week date style.  What I have tried to achieve with this patch, is to
> > allow users of Postgres to {specify|describe|operate on} dates in
> > either the Gregorian or week date calendars, as they prefer.  It
> > really depends on context whether the Gregorian or week date is more
> > desirable.
> >
> > As far as I know, the standard only provides for one numeric
> > representation of the "day of week", which begins the week at Monday =
> > 1 and ends at Sunday = 7.  Other conventions currently supported in
>
> IIRC, Sunday = 0 would be valid according to ISO 8601. I don't have the
> spec available ATM, though. OTOH, I'm quite sure ISO 8601 specifies
> weeks to start at monday... Odd that they (apparently) don't start
> counting from 0.

It's not odd.  We don't start counting months or weeks from zero.

I can't speak for the authors of 8601, but the numbering of months,
weeks, and days is the same as their ordinal position, so day "1" is
the "first" day, day "2" the second, and so on.  This numbering system
lends itself well to natural descriptions of dates; 2006-W12-1 can be
readily understood as meaning "the first day of the twelfth week of
two thousand six".

> Did you also take the (rather complicated) week numbering schemes into
> account? I'm not even sure that this defers from Gregorian week numbers,
> if something like that even exists.

The week numbering was already implemented in Postgres when I proposed
these features.  See the formatting fields "IYYY" and "IW" in the
docs, take a look at my original proposal at
http://archives.postgresql.org/pgsql-general/2006-10/msg00028.php and
the patch I submitted at
http://archives.postgresql.org/pgsql-patches/2006-11/msg00050.php

For more information about how week numbering works, see:

http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/ISO_week_date