Re: Week number

On Wed, Mar 14, 2001 at 02:50:35PM +0000, Thomas Lockhart wrote:

First day in week is Monday in ISO week.
Thomas, we have ISO week-of-year (IW in to_char or 'week' in date_part),
but we haven't ISO day-of-week (may be as 'ID' for to_char).
TODO for 7.2?
..but in ISO is 0-6; 0=Mon

I've been ignoring this until now, hoping no one would notice ;)

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

The implementation predates our support of ISO dates so it was not an
issue then.

date_part() is modeled on Ingres' implementation, but my old Ingres
manual indicates that 'dow' is not one of the options.

Should we change the definition of "dow", or implement another choice,
say "idow"?

Yes, I agree with new "idow" for date_part() and 'ID' for to_char() stuff.

My note grow up when I do SQL query that say something like:

"2001-03-12 is begin of week and it's second day of week" .. this sound
very curious :-)

test=# select to_char('2001-03-12'::date, 'IW Dth Day');
to_char
------------------
11 2nd Monday
(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

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 ]

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 ]

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/

"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

"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

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

I believe that in most European countries, Monday is the first day of the
week.

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2501
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

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