
> Unix day-of-week starts on Sunday, not Monday, which is what
> date_trunc('dow',...) returns. Presumably this is modeled on the
> traditional notion (at least in the US; I suspect this is true in most
> European countries at least) of Sunday being "the first day of week".

Germany and Austria have Monday as first day of week, I think most of 
Europe also.

Andreas

Wed, 14 Mar 2001 16:54:54 +0100
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: Re: Week number
On Wed, Mar 14, 2001 at 04:54:54PM +0100, Zeugswetter Andreas SB wrote:
> > Unix day-of-week starts on Sunday, not Monday, which is what
> > date_trunc('dow',...) returns. Presumably this is modeled on the
> > traditional notion (at least in the US; I suspect this is true in most
> > European countries at least) of Sunday being "the first day of week".
> 
> Germany and Austria have Monday as first day of week, I think most of 
> Europe also.

it is all relative.

most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat".

the concept of "first" day of week is a bit muddied.

many christian-influenced places would consider Sunday to be the "first"
day of the week, but monday being the "first" business day of the week.

i have seen calendars which use "Mon Tue Wed Thu Fri Sat Sun", and i have
worked with people where saturday was the first day of business. and also
places where sunday is the first day of business.

so, suffice to say, there is no "proper" first day of the week.

as such, the unix day of week pegs sunday as day 0, your code should just
use that index.  since almost all cultures have now adapted to a 7 day week
and a 365 day year, there shouldn't bee too much confusion.

-- 
[ Jim Mercer          jim@pneumonoultramicroscopicsilicovolcanoconiosis.ca ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[ aka                        jim@reptiles.org              +1 416 410-5633 ]

Wed, 14 Mar 2001 12:00:38 -0500
Jim Mercer <jim@reptiles.org>
Re: Re: Week number
On Wed, Mar 14, 2001 at 07:02:41PM +0100, Peter Eisentraut wrote:
> Jim Mercer writes:
> > most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat".
> 
> Most *English* calendars you have seen, I suppose.  In Germany there is no
> such possible calendar.  If you printed a calendar that way, it would be
> considered a printo.  The same is true in most parts of the continent.

i stand corrected.  i haven't had much dealings with european business.

-- 
[ Jim Mercer          jim@pneumonoultramicroscopicsilicovolcanoconiosis.ca ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[ aka                        jim@reptiles.org              +1 416 410-5633 ]

Wed, 14 Mar 2001 13:01:00 -0500
Jim Mercer <jim@reptiles.org>
Re: Re: Week number
Jim Mercer writes:

> most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat".

Most *English* calendars you have seen, I suppose.  In Germany there is no
such possible calendar.  If you printed a calendar that way, it would be
considered a printo.  The same is true in most parts of the continent.

The POSIX numbering (0-6) is actually pretty slick because it allows both
versions to work:  In the U.S. (e.g.) you get a natural order starting at
0, in Germany (e.g.) you get Monday as #1.

> so, suffice to say, there is no "proper" first day of the week.

There is a proper ISO first day of the week.  In many parts of Europe, the
day of the week + week of the year are real, official concepts.  E.g., you
would mark business transactions as "week x, day y" instead of with a date
(notice how this simplifies arithmetic).  Without trying to push through
my cultural bias, I think these applications should have some priority
over making up a solution that satisfies everybody but doesn't actually
suit any real application.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Wed, 14 Mar 2001 19:02:41 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Re: Week number
>>>>> "AZ" == Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

    >> Unix day-of-week starts on Sunday, not Monday, which is what
    >> date_trunc('dow',...) returns. Presumably this is modeled on
    >> the traditional notion (at least in the US; I suspect this is
    >> true in most European countries at least) of Sunday being "the
    >> first day of week".

    AZ> Germany and Austria have Monday as first day of week, I think
    AZ> most of Europe also.

I believe the goal was to have a to_char() that was complete and
Oracle-compatible.  Perhaps we need to also have a trunc() which is
Oracle compatible.  I haven't been playing with 7.1beta, but 7.0
trunc() doesn't like timestamps.  In Oracle, I can say

    select trunc(sysdate) - trunc(sysdate,'ww') + 1 from dual;

to get Monday=1.

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
rbroberts@acm.org                          Forest Hills, NY 11375

14 Mar 2001 13:23:30 -0500
Roland Roberts <roland@astrofoto.org>
Re: AW: Re: Week number
>>>>> "Peter" == Peter Eisentraut <peter_e@gmx.net> writes:

    Peter> The POSIX numbering (0-6) is actually pretty slick because
    Peter> it allows both versions to work: In the U.S. (e.g.) you get
    Peter> a natural order starting at 0, in Germany (e.g.) you get
    Peter> Monday as #1.

Oracle's to_char() supports format IW for the ISO week of the year,
but there is no equivalent ID for the ISO day of the week.  Perhaps
this should be a PostgreSQL extension?

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
rbroberts@acm.org                          Forest Hills, NY 11375

14 Mar 2001 13:34:08 -0500
Roland Roberts <roland@astrofoto.org>
Re: Re: Week number
On Wed, Mar 14, 2001 at 01:23:30PM -0500, Roland Roberts wrote:
> >>>>> "AZ" == Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> 
>     >> Unix day-of-week starts on Sunday, not Monday, which is what
>     >> date_trunc('dow',...) returns. Presumably this is modeled on
>     >> the traditional notion (at least in the US; I suspect this is
>     >> true in most European countries at least) of Sunday being "the
>     >> first day of week".
> 
>     AZ> Germany and Austria have Monday as first day of week, I think
>     AZ> most of Europe also.
> 
> I believe the goal was to have a to_char() that was complete and
> Oracle-compatible.  Perhaps we need to also have a trunc() which is

 Yes, an Oracle-compatiblity is important for masks (format pictures)
used in both (Ora and PG). But our PG's implementation has some extensions,
for example 'ID' ISO-day-of-week in 7.2 where Monday = first day of week.
I hope all countries will glad :-)


 for 'WW' and 'D' are results same:

Ora:

SVRMGR> select to_char( to_date('2001/03/12', 'YYYY/MM/DD'), 'WW Day D
YYYY/MM/DD') from dual;
TO_CHAR(TO_DATE('2001/03/
-------------------------
11 Monday    2 2001/03/12
1 row selected.

PG:

select to_char( to_date('2001/03/12', 'YYYY/MM/DD'), 'WW Day D YYYY/MM/DD');
          to_char
---------------------------
 11 Monday    2 2001/03/12
(1 row)

		Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Thu, 15 Mar 2001 10:58:11 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: AW: Re: Week number