You can really do it using CASE:
CASE
WHEN dow() = 0 THEN 6
ELSE dow() - 1
END
---------------------------------------------------------------------------
me@alternize.com wrote:
> hi list
>
> from the user manual:
> ---------------------------------------
> dow
> The day of the week (0 - 6; Sunday is 0) (for timestamp values only)
> SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
> Result: 5
> Note that extract's day of the week numbering is different from that of
> the to_char function.
> ---------------------------------------
>
> now it happens that in europe it is common to have monday as the first day
> of week. as far as i know, mssql uses the local os setting for determining
> which value should be returned for a sunday. one select statement we're
> trying to migrate from mssql to pgsql depends on the ordering of records
> according to their day of week value - where monday is the first day. how
> can this be fixed in pgsql?
>
> the (simplified) query in question:
> SELECT date_part('year', date_added) AS year, date_part('week', date_added)
> AS week, date_part('dow', date_added) AS day, id FROM entries ORDER BY year,
> week, day
>
> btw: date_part('week', ...) already recognizes monday being the first day of
> week accordingly to ISO-8601.
>
> thanks,
> thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
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