Re: extract (dow/week from date) - Mailing list pgsql-general

2005/8/21, Stephan Szabo <sszabo@megazone.bigpanda.com>:
> On Sat, 20 Aug 2005, Tom Lane wrote:
>
> > Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:
> > > I'm ordering by date just to show that sunday, the 0th day of the
> > > week, is the last day of a given week, which is not what I need.
> >
> > extract(week) follows the ISO definition of week, which is pretty
> > strange anyway, but in particular it says that weeks start on Monday.
> > extract(dow) follows a different convention.  There's not a lot we
> > can do about this --- we're certainly not going to change extract(week),
> > and I can't see changing extract(dow) either.
>
> Instead of change the existing ones, couldn't we add a new extract format
> for "iso day of week" that returns 1-7 for monday-sunday that would be
> consistent with the week definition?
>
It would work for me. The problem is not if is sunday or monday the
first day of the week, but to make all days of the week from extract
(dow) (or a new extract (isodow)) fit into the same week from extract
(week). It does not happen now:

drop table dates;
create table dates (date timestamp);
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
  to_char (date, 'Dy') as cday,
  extract (week from date) as eweek,
  extract (dow from date) as edow,
  to_char (date, 'WW')::int as cweek,
  to_char (date, 'D')::int as cdow
from dates
order by date;

        date         | cday | eweek | edow | cweek | cdow
---------------------+------+-------+------+-------+------
 2004-12-31 00:00:00 | Fri  |    53 |    5 |    53 |    6
 2005-01-01 00:00:00 | Sat  |    53 |    6 |     1 |    7
 2005-01-02 00:00:00 | Sun  |    53 |    0 |     1 |    1
 2005-01-03 00:00:00 | Mon  |     1 |    1 |     1 |    2
 2005-01-04 00:00:00 | Tue  |     1 |    2 |     1 |    3
 2005-01-05 00:00:00 | Wed  |     1 |    3 |     1 |    4
 2005-01-06 00:00:00 | Thu  |     1 |    4 |     1 |    5
 2005-01-07 00:00:00 | Fri  |     1 |    5 |     1 |    6
 2005-01-08 00:00:00 | Sat  |     1 |    6 |     2 |    7
 2005-01-09 00:00:00 | Sun  |     1 |    0 |     2 |    1
(10 rows)

There is the same mismatch in to_char ('WW') related to to_char ('D')

Of course it would be even better if we could pass parameters to the
functions changing its behavior such as sunday/monday as the first day
or 0-1 as the first day.

Regards, Clodoaldo Pinto