Thread: AW: Re: Week number
> 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
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 ]
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/
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 ]
>>>>> "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
>>>>> "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
ismodeled 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
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