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