BUG #2977: dow doesn't conform to ISO-8601

Am Mittwoch, 7. Februar 2007 11:24 schrieb Adriaan van Os:

Section 9.9.1 of the Postgres docs
<http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html&gt;
mentions that the "week" subfield of the date_part function conforms to the
ISO-8601 standard.

I see no such claim there.

You can not, in the same function, ignore ISO-8601 for one subfield and
follow it in another.

I think we have pretty well shown that we can.

Besides, if in the same week Sunday comes before Monday, how can the result
of the "week" and "dow" fields conform to each other ?

They don't.

If "dow" can not be changed for reasons of backward compatibility, I
suggest a new subfield "dayofweek" that does conform to the standard.

That might be reasonable. (Or maybe "isodow".)

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

I have implemented 'isodow' with the attached patch.

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

Peter Eisentraut wrote:

Am Mittwoch, 7. Februar 2007 11:24 schrieb Adriaan van Os:

Section 9.9.1 of the Postgres docs
<http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html&gt;
mentions that the "week" subfield of the date_part function conforms to the
ISO-8601 standard.

I see no such claim there.

You can not, in the same function, ignore ISO-8601 for one subfield and
follow it in another.

I think we have pretty well shown that we can.

Besides, if in the same week Sunday comes before Monday, how can the result
of the "week" and "dow" fields conform to each other ?

They don't.

If "dow" can not be changed for reasons of backward compatibility, I
suggest a new subfield "dayofweek" that does conform to the standard.

That might be reasonable. (Or maybe "isodow".)

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

Updated version applied. I reduced the numering changes for the macros.

There was also documentation text for "dow" and a few others that said
"(for <type>timestamp</type> values only)", but in fact the field worked
for "timestamptz" and "date" too, so I removed the mentions. If people
get confused, I will come up with new wording, like "doesn't work for
interval or time", which I think is pretty obvious. I remember people
got confused in the past about this, so maybe we still need something.

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

Bruce Momjian wrote:

I have implemented 'isodow' with the attached patch.

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

Peter Eisentraut wrote:

Am Mittwoch, 7. Februar 2007 11:24 schrieb Adriaan van Os:

Section 9.9.1 of the Postgres docs
<http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html&gt;
mentions that the "week" subfield of the date_part function conforms to the
ISO-8601 standard.

I see no such claim there.

You can not, in the same function, ignore ISO-8601 for one subfield and
follow it in another.

I think we have pretty well shown that we can.

Besides, if in the same week Sunday comes before Monday, how can the result
of the "week" and "dow" fields conform to each other ?

They don't.

If "dow" can not be changed for reasons of backward compatibility, I
suggest a new subfield "dayofweek" that does conform to the standard.

That might be reasonable. (Or maybe "isodow".)

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

Bruce Momjian wrote:

Updated version applied. I reduced the numering changes for the macros.

There was also documentation text for "dow" and a few others that said
"(for <type>timestamp</type> values only)", but in fact the field worked
for "timestamptz" and "date" too, so I removed the mentions. If people
get confused, I will come up with new wording, like "doesn't work for
interval or time", which I think is pretty obvious. I remember people
got confused in the past about this, so maybe we still need something.

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

Bruce Momjian wrote:

I have implemented 'isodow' with the attached patch.

Thanks for the patch and the new function.

Adriaan van Os

FYI, 8.3 will have an 'isodow' that conforms to ISO week start:

test=> select current_date-2, date_part('dow', current_date-2),
test-> date_part('isodow', current_date-2);
?column? | date_part | date_part
------------+-----------+-----------
2007-03-25 | 0 | 7
(1 row)

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

Adriaan van Os wrote:

The following bug has been logged online:

Bug reference: 2977
Logged by: Adriaan van Os
Email address: postgres@microbizz.nl
PostgreSQL version: 8.1.4
Operating system: Mac OS X 10.4.6, intel
Description: dow doesn't conform to ISO-8601
Details:

Section 9.9.1 of the Postgres docs
<http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html&gt;
mentions that the "week" subfield of the date_part function conforms to the
ISO-8601 standard.

The same ISO-8601 standard <http://www.omg.org/docs/ISO-stds/06-08-01.pdf&gt;
defines on page 9 in table-2 of section 3.2.2 that Monday is day 1 and
Sunday day seven. However, the "dow" subfield of the date_part function
returns 0 for Sunday.

You can not, in the same function, ignore ISO-8601 for one subfield and
follow it in another.

Besides, if in the same week Sunday comes before Monday, how can the result
of the "week" and "dow" fields conform to each other ?

If "dow" can not be changed for reasons of backward compatibility, I suggest
a new subfield "dayofweek" that does conform to the standard.

Adriaan van Os

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