Re: ISO week dates

Hey guys,

I have a question regarding the ISO 8601 week date format. Outputting dates
in this format seems to be partially supported, and rather inconsistent.
The documentation for to_char() lists 'IYYY' (ISO year) and 'IW' (ISO week)
as format patterns, but there is no "ISO day of week" format pattern to
complete the set.

A full ISO week date is written as "<year>-W<week>-<day>", where <day> is
the day of week with Monday = 1 and Sunday = 7.

The format pattern 'D' does not help, since it numbers weekdays beginning at
Sunday = 1.

You could use the extract() function instead, but again, support is partial
and inconsistent. You can get the right day of week by using the 'dow'
field and adding one, the 'week' field returns the ISO week, but the 'year'
field returns the Gregorian year!

So to_char() has the ISO year and week, but not the day. extract() has ISO
day (sort of) and week, but not the year.

Granted you can put a working ISO format together by using both functions;
something like

create function to_iso(timestamp) returns text as $$
SELECT to_char('IYYY', $1) || '-W' || extract(week, $1) || '-' ||
(extract(dow, $1) + 1)
$$ language sql immutable;

... but this seems unnecessarily awkward. Why not:

* 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'?

Regards,
BJ

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(at)lelarge(dot)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(at)lelarge(dot)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(at)lelarge(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)magproductions(dot)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(at)magproductions(dot)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(at)magproductions(dot)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(at)magproductions(dot)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(at)magproductions(dot)nl> wrote:
> Brendan Jurd wrote:
> > On 11/23/06, Alban Hertroys <alban(at)magproductions(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Brendan Jurd wrote:
> 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.

On these questions, we have to find out how Oracle handles it, but your
approach seems appropriate.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian wrote:
> On these questions, we have to find out how Oracle handles it, but
> your approach seems appropriate.

I don't think Oracle even has that. But personally I'd like to see
errors for invalid pattern combinations.

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

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > On these questions, we have to find out how Oracle handles it, but
> > your approach seems appropriate.
>
> I don't think Oracle even has that. But personally I'd like to see
> errors for invalid pattern combinations.

What do we do with other invalid pattern combinations in to_char() now?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Peter Eisentraut wrote:
>> I don't think Oracle even has that. But personally I'd like to see
>> errors for invalid pattern combinations.

> What do we do with other invalid pattern combinations in to_char() now?

Mostly, we return bogus results :-(. The formatting.c code in general
doesn't seem very robust.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Peter Eisentraut wrote:
> >> I don't think Oracle even has that. But personally I'd like to see
> >> errors for invalid pattern combinations.
>
> > What do we do with other invalid pattern combinations in to_char() now?
>
> Mostly, we return bogus results :-(. The formatting.c code in general
> doesn't seem very robust.

Yep, seems every release I am in there cleaning up some mistake repeated
multiple times in the code. It needs a good cleaning.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > Peter Eisentraut wrote:
> > >> I don't think Oracle even has that. But personally I'd like to see
> > >> errors for invalid pattern combinations.
> >
> > > What do we do with other invalid pattern combinations in to_char() now?
> >
> > Mostly, we return bogus results :-(. The formatting.c code in general
> > doesn't seem very robust.
>
> Yep, seems every release I am in there cleaning up some mistake repeated
> multiple times in the code. It needs a good cleaning.

I'm happy to volunteer to do something about the invalid field
combinations, but I suspect an overhaul of formatting.c is more than I
can currently chew. I figure it would be a bit misguided of me to put
together a patch for invalid field combinations if somebody is about
to do a rewrite of much of the code?

Brendan Jurd wrote:
> On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > Peter Eisentraut wrote:
> > > >> I don't think Oracle even has that. But personally I'd like to see
> > > >> errors for invalid pattern combinations.
> > >
> > > > What do we do with other invalid pattern combinations in to_char() now?
> > >
> > > Mostly, we return bogus results :-(. The formatting.c code in general
> > > doesn't seem very robust.
> >
> > Yep, seems every release I am in there cleaning up some mistake repeated
> > multiple times in the code. It needs a good cleaning.
>
> I'm happy to volunteer to do something about the invalid field
> combinations, but I suspect an overhaul of formatting.c is more than I
> can currently chew. I figure it would be a bit misguided of me to put
> together a patch for invalid field combinations if somebody is about
> to do a rewrite of much of the code?

Yea, I was just throwing out a note that someday if someone has time,
that file need a good sweeping.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian wrote:
> Brendan Jurd wrote:
> > On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > Tom Lane wrote:
> > > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > > Peter Eisentraut wrote:
> > > > >> I don't think Oracle even has that. But personally I'd like to see
> > > > >> errors for invalid pattern combinations.
> > > >
> > > > > What do we do with other invalid pattern combinations in to_char() now?
> > > >
> > > > Mostly, we return bogus results :-(. The formatting.c code in general
> > > > doesn't seem very robust.
> > >
> > > Yep, seems every release I am in there cleaning up some mistake repeated
> > > multiple times in the code. It needs a good cleaning.
> >
> > I'm happy to volunteer to do something about the invalid field
> > combinations, but I suspect an overhaul of formatting.c is more than I
> > can currently chew. I figure it would be a bit misguided of me to put
> > together a patch for invalid field combinations if somebody is about
> > to do a rewrite of much of the code?
>
> Yea, I was just throwing out a note that someday if someone has time,
> that file need a good sweeping.

Sorry, I wasn't clear. No one is currently working on overhauling
formatting.c, so if you want to submit _any_ patch to improve the file,
please do. :-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Bruce Momjian wrote:
> > Brendan Jurd wrote:
> > > On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > > Tom Lane wrote:
> > > > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > > > Peter Eisentraut wrote:
> > > > > >> I don't think Oracle even has that. But personally I'd like to see
> > > > > >> errors for invalid pattern combinations.
> > > > >
> > > > > > What do we do with other invalid pattern combinations in to_char() now?
> > > > >
> > > > > Mostly, we return bogus results :-(. The formatting.c code in general
> > > > > doesn't seem very robust.
> > > >
> > > > Yep, seems every release I am in there cleaning up some mistake repeated
> > > > multiple times in the code. It needs a good cleaning.
> > >
> > > I'm happy to volunteer to do something about the invalid field
> > > combinations, but I suspect an overhaul of formatting.c is more than I
> > > can currently chew. I figure it would be a bit misguided of me to put
> > > together a patch for invalid field combinations if somebody is about
> > > to do a rewrite of much of the code?
> >
> > Yea, I was just throwing out a note that someday if someone has time,
> > that file need a good sweeping.
>
> Sorry, I wasn't clear. No one is currently working on overhauling
> formatting.c, so if you want to submit _any_ patch to improve the file,
> please do. :-)

It'll be a pleasure Bruce. I think it would be best to wait until the
existing ISO week date patch has gone through before working on it
though.

Perhaps a TODO item is in order?

Brendan Jurd wrote:
> On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Bruce Momjian wrote:
> > > Brendan Jurd wrote:
> > > > On 2/15/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > > > Tom Lane wrote:
> > > > > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > > > > Peter Eisentraut wrote:
> > > > > > >> I don't think Oracle even has that. But personally I'd like to see
> > > > > > >> errors for invalid pattern combinations.
> > > > > >
> > > > > > > What do we do with other invalid pattern combinations in to_char() now?
> > > > > >
> > > > > > Mostly, we return bogus results :-(. The formatting.c code in general
> > > > > > doesn't seem very robust.
> > > > >
> > > > > Yep, seems every release I am in there cleaning up some mistake repeated
> > > > > multiple times in the code. It needs a good cleaning.
> > > >
> > > > I'm happy to volunteer to do something about the invalid field
> > > > combinations, but I suspect an overhaul of formatting.c is more than I
> > > > can currently chew. I figure it would be a bit misguided of me to put
> > > > together a patch for invalid field combinations if somebody is about
> > > > to do a rewrite of much of the code?
> > >
> > > Yea, I was just throwing out a note that someday if someone has time,
> > > that file need a good sweeping.
> >
> > Sorry, I wasn't clear. No one is currently working on overhauling
> > formatting.c, so if you want to submit _any_ patch to improve the file,
> > please do. :-)
>
> It'll be a pleasure Bruce. I think it would be best to wait until the
> existing ISO week date patch has gone through before working on it
> though.

OK.

> Perhaps a TODO item is in order?

Sure, good suggestion.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Patch applied. Thanks.

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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