Re: extract (dow/week from date)

2005/8/20, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>