Re: extract (dow/week from date)

2005/8/20, Bruce Momjian <pgman@candle.pha.pa.us>:
>
> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
>

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.

I'm migrating a site from mysql to postgres and the logic of the table
at the page works for Sunday as the first day of the week. I will make
the pg query produce the same behavior as the mysql query but the pg
function behavior seems confusing.

Note how are the dates sorted at the table:
http://fahstats.com/tp.php?t=13802

Regards, Clodoaldo Pinto

2005/8/20, Bruce Momjian <pgman@candle.pha.pa.us>:
>
> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
>
> ---------------------------------------------------------------------------
>
> 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);
> >
> > Regards,
> > Clodoaldo Pinto
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>