Re: extract (dow/week from date)

On Sat, 20 Aug 2005, Bruce Momjian wrote:

> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.

That's not really the issue. The issue is that our definition of date of
week and week of year are somewhat inconsistent with each other. We appear
to be doing week of year per ISO-8601, but what the descriptions I've seen
of that use days 1-7 for Monday-Sunday, whereas we're apparently giving
0-6 for Sunday-Saturday. This means that sorting by (week of year, day of
week) will sort Sundays oddly (since it would for example below sort the
14th before the 8th).

> ---------------------------------------------------------------------------
>
> 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);