2005/8/20, Bruce Momjian <pgman@xxxxxxxxxxxxxxxx>: > > 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@xxxxxxxxxxxxxxxx>: > > 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@xxxxxxxxxxxxxxxx | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
