Postgres Pro
Downloads
Home   >   mailing lists

Thread: help with date_part & day of week

help with date_part & day of week

From
Date:
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

Re: help with date_part & day of week

From
Bruce Momjian
Date:
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

Re: help with date_part & day of week

From
Date:
> You can really do it using CASE:
>
> CASE
> WHEN dow() = 0 THEN 6
> ELSE dow() - 1
> END

how good is this solution in regards to performance? i always thought CASE
and the such should only be used for last resorts. and now there is also a
date function involved that gets twice in 6/7 of all cases...

regarding these concerns, can you give me your opinion on this solution i
came up:

SELECT ((date_part('dow', now()) + 6) % 7) AS weekday

thanks,
thomas

Re: help with date_part & day of week

From
Bruce Momjian
Date:
me@alternize.com wrote:
> > You can really do it using CASE:
> >
> > CASE
> > WHEN dow() = 0 THEN 6
> > ELSE dow() - 1
> > END
>
> how good is this solution in regards to performance? i always thought CASE
> and the such should only be used for last resorts. and now there is also a
> date function involved that gets twice in 6/7 of all cases...
>
> regarding these concerns, can you give me your opinion on this solution i
> came up:
>
> SELECT ((date_part('dow', now()) + 6) % 7) AS weekday

I don't think CASE is any significant performance hit, certainly less
than a pl/pgsql function.  Anyway, your solution looks even cleaner.

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