ISO week dates

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?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut a ecrit le 07/10/2006 09:01:

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?

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
to_char(('2006-10-'||a+2)::date, 'D') as "D field"
from generate_series(1, 15) as a;
dow | ID field | D field
-----------+----------+---------
TUESDAY | 2 | 3
WEDNESDAY | 3 | 4
THURSDAY | 4 | 5
FRIDAY | 5 | 6
SATURDAY | 6 | 7
SUNDAY | 7 | 1
MONDAY | 1 | 2
TUESDAY | 2 | 3
WEDNESDAY | 3 | 4
THURSDAY | 4 | 5
FRIDAY | 5 | 6
SATURDAY | 6 | 7
SUNDAY | 7 | 1
MONDAY | 1 | 2
TUESDAY | 2 | 3
(15 rows)

I just want to know if my patch is interesting... and if it's OK, I can
work on the ISO year field.

Regards.

--
Guillaume.

Guillaume Lelarge wrote:

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

I haven't been following this thread, but I just wanted to point out
that we prefer context diffs.

Please resend the patch as a context diff, using "diff -c" or "cvs diff
-c", so that it's easier to review.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas a ecrit le 12/10/2006 12:43:

Guillaume Lelarge wrote:

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

I haven't been following this thread, but I just wanted to point out
that we prefer context diffs.

Please resend the patch as a context diff, using "diff -c" or "cvs diff
-c", so that it's easier to review.

Sorry, you're right. Here it is.

--
Guillaume.

Guillaume Lelarge wrote:

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
to_char(('2006-10-'||a+2)::date, 'D') as "D field"
from generate_series(1, 15) as a;

There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
is the two-digit ISO week, but 'ID' would be the one-digit ISO
day-of-the-week. I'm not sure we can fix that, but I wanted to point
it out.

We should also support a format for ISO day-of-the-year, which might
be 'IDDD'.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut a �crit :

Guillaume Lelarge wrote:

I've tried to work on the first one, the ISO day field. My patch is
attached and is against CVS HEAD. It only takes care of the code,
nothing is done for documentation matter. It works with me :

toto=# select to_char(('2006-10-'||a+2)::date, 'DAY') as "dow",
to_char(('2006-10-'||a+2)::date, 'ID') as "ID field",
to_char(('2006-10-'||a+2)::date, 'D') as "D field"
from generate_series(1, 15) as a;

There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
is the two-digit ISO week, but 'ID' would be the one-digit ISO
day-of-the-week. I'm not sure we can fix that, but I wanted to point
it out.

Is there a two digit ISO day of the week ? If not, we should use ID. As
you say, I don't know what we can do about that. I used Brendan Jurd's
idea, perhaps he can tell us more on this matter.

We should also support a format for ISO day-of-the-year, which might
be 'IDDD'.

I will work tomorrow on this one.

Regards.

--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->

Guillaume Lelarge a ecrit le 12/10/2006 20:20:

Peter Eisentraut a �crit :

We should also support a format for ISO day-of-the-year, which might
be 'IDDD'.

I will work tomorrow on this one.

Don't we already have it ? It seems ISO day-of-the-year is between 001
and 366 in leap years. Isn't this the definition for DDD format ? if Im'
right, I just need to add the IDDD pattern ?

--
Guillaume.

Guillaume Lelarge wrote:

Guillaume Lelarge a ecrit le 12/10/2006 20:20:

Peter Eisentraut a �crit :

We should also support a format for ISO day-of-the-year, which
might be 'IDDD'.

I will work tomorrow on this one.

Don't we already have it ? It seems ISO day-of-the-year is between
001 and 366 in leap years. Isn't this the definition for DDD format ?
if Im' right, I just need to add the IDDD pattern ?

The ISO 8601 day-of-the-year is aligned with the week-of-the-year. It
should be the case that day one of week one is also day one of the
year.

(As a particular example, day one of 2006 is January 2, 2006.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 10/13/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Peter Eisentraut a écrit :

There is an inconsistency here: 'IYYY' is the four-digit ISO year, 'IW'
is the two-digit ISO week, but 'ID' would be the one-digit ISO
day-of-the-week. I'm not sure we can fix that, but I wanted to point
it out.

Is there a two digit ISO day of the week ? If not, we should use ID. As
you say, I don't know what we can do about that. I used Brendan Jurd's
idea, perhaps he can tell us more on this matter.

Thanks for your work so far Guillaume. I agree with Peter, it is
inconsistent to have a one-digit field represented by a two-character
code. However, I don't see a way around it. 'D' is already taken to
mean the non-ISO day-of-week, and 'I' is taken to mean the last digit
of the ISO year (although to be honest I don't see where this would be
useful).

This sort of thing is not unprecedented in to_char(). For example,
the codes 'HH24' and 'HH12' are four characters long, but resolve to a
two-digit result. 'DAY' resolves to nine characters, and so on.

Basically I think we're stuck with ID for day-of-week and IDDD for day-of-year.

I will take a look at implementing 'isoyear' for extract(), and also
start putting together a patch for the documentation. If Guillaume is
still interested in adding the IDDD field to to_char(), wonderful, if
not I will pick up from his ID patch and add IDDD to it.

Regards,
BJ

Brendan Jurd a �crit :

On 10/13/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Peter Eisentraut a �crit :

There is an inconsistency here: 'IYYY' is the four-digit ISO year,

'IW'

is the two-digit ISO week, but 'ID' would be the one-digit ISO
day-of-the-week. I'm not sure we can fix that, but I wanted to point
it out.

Is there a two digit ISO day of the week ? If not, we should use ID. As
you say, I don't know what we can do about that. I used Brendan Jurd's
idea, perhaps he can tell us more on this matter.

Thanks for your work so far Guillaume. I agree with Peter, it is
inconsistent to have a one-digit field represented by a two-character
code. However, I don't see a way around it. 'D' is already taken to
mean the non-ISO day-of-week, and 'I' is taken to mean the last digit
of the ISO year (although to be honest I don't see where this would be
useful).

This sort of thing is not unprecedented in to_char(). For example,
the codes 'HH24' and 'HH12' are four characters long, but resolve to a
two-digit result. 'DAY' resolves to nine characters, and so on.

Basically I think we're stuck with ID for day-of-week and IDDD for
day-of-year.

I will take a look at implementing 'isoyear' for extract(), and also
start putting together a patch for the documentation. If Guillaume is
still interested in adding the IDDD field to to_char(), wonderful, if
not I will pick up from his ID patch and add IDDD to it.

Sorry for the late answer. I'm still interested but, to be honest, I
don't think I will have the time to do it. Perhaps in a month or so.

Regards.

--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->

On 11/9/06, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Brendan Jurd a écrit :

I will take a look at implementing 'isoyear' for extract(), and also
start putting together a patch for the documentation. If Guillaume is
still interested in adding the IDDD field to to_char(), wonderful, if
not I will pick up from his ID patch and add IDDD to it.

Sorry for the late answer. I'm still interested but, to be honest, I
don't think I will have the time to do it. Perhaps in a month or so.

No problem Guillaume. I'm actually nearly done adding in all these
features. Thank you for getting the ball rolling!

The attached patch implements my proposal to extend support for the
ISO week date calendar.

I have added two new format fields for use with to_char, to_date and
to_timestamp:
- ID for day-of-week
- IDDD for day-of-year

This makes it possible to convert ISO week dates to and from text
fully represented in either week ('IYYY-IW-ID') or day-of-year
('IYYY-IDDD') format.

I have also added an 'isoyear' field for use with extract / date_part.

The patch includes documentation updates and some extra tests in the
regression suite for the new fields.

I have tried to implement these features with as little disruption to
the existing code as possible. I built on the existing date2iso*
functions in src/backend/utils/adt/timestamp.c, and added a few
functions of my own, but I wonder if these functions would be more
appropriately located in datetime.c, alongside date2j and j2date?

I'd also like to raise the topic of how conversion from text to ISO
week dates should be handled, where the user has specified a bogus
mixture of fields. Existing code basically ignores these issues; for
example, if a user were to call to_date('1998-01-01 2454050',
'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
the year field from YYYY, then overwriting year, month and day with
the values from the Julian date in J, then setting the month and day
normally from MM and DD.

2006-01-01 is not a valid representation of either of the values the
user specified. Now you might say "ask a silly question, get a silly
answer"; the user shouldn't send nonsense arguments to to_date and
expect a sensible result. But perhaps the right way to respond to a
broken timestamp definition is to throw an error, rather than behave
as though everything has gone to plan, and return something which is
not correct.

The same situation can arise if the user mixes ISO and Gregorian data;
how should Postgres deal with something like to_date('2006-250',
'IYYY-DDD')? The current behaviour in my patch is actually to assume
that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
of the ISO year 2006" is total gibberish. But perhaps it should be
throwing an error message.

That's all for now, thanks for your time.
BJ

This has been saved for the 8.3 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Brendan Jurd wrote:

The attached patch implements my proposal to extend support for the
ISO week date calendar.

I have added two new format fields for use with to_char, to_date and
to_timestamp:
- ID for day-of-week
- IDDD for day-of-year

This makes it possible to convert ISO week dates to and from text
fully represented in either week ('IYYY-IW-ID') or day-of-year
('IYYY-IDDD') format.

I have also added an 'isoyear' field for use with extract / date_part.

The patch includes documentation updates and some extra tests in the
regression suite for the new fields.

I have tried to implement these features with as little disruption to
the existing code as possible. I built on the existing date2iso*
functions in src/backend/utils/adt/timestamp.c, and added a few
functions of my own, but I wonder if these functions would be more
appropriately located in datetime.c, alongside date2j and j2date?

I'd also like to raise the topic of how conversion from text to ISO
week dates should be handled, where the user has specified a bogus
mixture of fields. Existing code basically ignores these issues; for
example, if a user were to call to_date('1998-01-01 2454050',
'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
the year field from YYYY, then overwriting year, month and day with
the values from the Julian date in J, then setting the month and day
normally from MM and DD.

2006-01-01 is not a valid representation of either of the values the
user specified. Now you might say "ask a silly question, get a silly
answer"; the user shouldn't send nonsense arguments to to_date and
expect a sensible result. But perhaps the right way to respond to a
broken timestamp definition is to throw an error, rather than behave
as though everything has gone to plan, and return something which is
not correct.

The same situation can arise if the user mixes ISO and Gregorian data;
how should Postgres deal with something like to_date('2006-250',
'IYYY-DDD')? The current behaviour in my patch is actually to assume
that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
of the ISO year 2006" is total gibberish. But perhaps it should be
throwing an error message.

That's all for now, thanks for your time.
BJ

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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'

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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?

I'm sorry for any extra noise, I don't remember much of the original
discussion (yeah, I know, archives...).

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

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.

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.

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

I hope that provides some added clarity.

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

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

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Brendan Jurd wrote:

The attached patch implements my proposal to extend support for the
ISO week date calendar.

I have added two new format fields for use with to_char, to_date and
to_timestamp:
- ID for day-of-week
- IDDD for day-of-year

This makes it possible to convert ISO week dates to and from text
fully represented in either week ('IYYY-IW-ID') or day-of-year
('IYYY-IDDD') format.

I have also added an 'isoyear' field for use with extract / date_part.

The patch includes documentation updates and some extra tests in the
regression suite for the new fields.

I have tried to implement these features with as little disruption to
the existing code as possible. I built on the existing date2iso*
functions in src/backend/utils/adt/timestamp.c, and added a few
functions of my own, but I wonder if these functions would be more
appropriately located in datetime.c, alongside date2j and j2date?

I'd also like to raise the topic of how conversion from text to ISO
week dates should be handled, where the user has specified a bogus
mixture of fields. Existing code basically ignores these issues; for
example, if a user were to call to_date('1998-01-01 2454050',
'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
the year field from YYYY, then overwriting year, month and day with
the values from the Julian date in J, then setting the month and day
normally from MM and DD.

2006-01-01 is not a valid representation of either of the values the
user specified. Now you might say "ask a silly question, get a silly
answer"; the user shouldn't send nonsense arguments to to_date and
expect a sensible result. But perhaps the right way to respond to a
broken timestamp definition is to throw an error, rather than behave
as though everything has gone to plan, and return something which is
not correct.

The same situation can arise if the user mixes ISO and Gregorian data;
how should Postgres deal with something like to_date('2006-250',
'IYYY-DDD')? The current behaviour in my patch is actually to assume
that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
of the ISO year 2006" is total gibberish. But perhaps it should be
throwing an error message.

That's all for now, thanks for your time.
BJ

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +