Thread: BUG #2977: dow doesn't conform to ISO-8601
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> 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> 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
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> > 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/
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>
> 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>
> 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. +