Re: ISO week dates - Mailing list pgsql-general

Brendan Jurd wrote:
> On 11/23/06, Alban Hertroys <alban@magproductions.nl> wrote:
>> Bruce Momjian wrote:
>> > Peter Eisentraut wrote:
>> >> Brendan Jurd wrote:
>> >>>  * add an ISO day format pattern to to_char() called 'ID', which
>> >>> starts at Monday = 1, and
>> >>>  * add an ISO year field to extract() called 'isoyear'?
>> >> That seems reasonable.  Do you volunteer?
>> >
>> > Added to TODO:
>> >
>> >       * Add ISO day of week format 'ID' to to_char() where Monday = 1
>> >       * Add an ISO year field to extract() called 'isoyear'
>>
>> 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.

> I'm not particularly attached to the name "isoyear".  If that is seen
> as too vague, perhaps "weekyear" or something similar would work
> better.  It can easily be changed at this stage.

That's not exactly the point I tried to make.

IMO whether gregorian or iso 8691 interpretation is used depends on user
settings; either locale or a client setting. After all, it only affects
the interpretation of the data.

Being able to force the interpretation to either representation in
queries would be useful too, I suppose.

> 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.

> Postgres, such as Sunday = 0 or Sunday = 1, are to my knowledge
> non-ISO.  Hence the addition of 'ID' to the formatting functions.

There doesn't seem to be much difference between Sunday = 0 and Sunday = 7.

> I hope that provides some added clarity.

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.

IIRC there are years with the first few days in the last week of the
previous year, and there are years that have week numbers go up to 53. I
recall the rule to be that if Januari 1st is before wednesday, it is
called week 1, and otherwise it is whatever the last week number of the
previous year was.

Can you understand why I don't trust week numbers in project planning? :P

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //