Re: extract (dow/week from date)

On Sat, Aug 20, 2005 at 09:03:32PM -0400, Bruce Momjian wrote:
>
> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.

I think the point is that extract(week) is inconsistent with
extract(dow) with respect to what week a sunday is on.

> Clodoaldo Pinto wrote:
> > The extract (dow from date) function returns 0 for Sunday (nice).
> >
> > My problem is that Sunday is the last day of the week according to
> > extract (week from date). Is it the expected behavior?
> >
> > teste=# create table dates (date timestamp);
> > CREATE TABLE
> > teste=# insert into dates values ('2005-08-08');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-09');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-10');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-11');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-12');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-13');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-14');
> > INSERT 0 1
> > teste=# select date, extract (week from date) as week, extract (dow
> > from date) as dow
> > teste-# from dates
> > teste-# order by date;
> > date | week | dow
> > ---------------------+------+-----
> > 2005-08-08 00:00:00 | 32 | 1
> > 2005-08-09 00:00:00 | 32 | 2
> > 2005-08-10 00:00:00 | 32 | 3
> > 2005-08-11 00:00:00 | 32 | 4
> > 2005-08-12 00:00:00 | 32 | 5
> > 2005-08-13 00:00:00 | 32 | 6
> > 2005-08-14 00:00:00 | 32 | 0
> > (7 rows)
> >
> > In mysql the date functions work as I need it:
> > order by yearweek(day, 2) desc, dayofweek(day);

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"