Re: day of week

On Tue, 6 Jun 2000, Peter Eisentraut wrote:

> Karel Zak writes:
>
> > The date_part() say that monday is a first day, to_char that it is
> > second day, and what will say date_trunc()? --- how date is a week
> > start, 'monday' or 'sunday' date ?
>
> In a perfect world, the answer would be locale dependent.

Hmm, I not sure with locale dependent --- if anyone use 'dow' in
some calculation he needs control over this number. For me is better
Tom's idea with SET.

> In many implementations Sunday is the first day of the week but counting
> starts with 0, so you still get Monday as "1".

All it is a little mazy, for example week-of-year:

Firts day of year:
=================

select to_char('2000-01-01'::timestamp, 'WW Day D');
to_char
----------------
00 Saturday 7 <----- '00' --- here I have bug

Oracle (8.0.5):
~~~~~~~~~~~~~~

SVRMGR> select to_char( to_date('31-Dec-1999', 'DD-MON-YYYY'), 'WW Day D')
from dual;
TO_CHAR(TO_DAT
--------------
53 Friday 6

SVRMGR> select to_char( to_date('01-Jan-2000', 'DD-MON-YYYY'), 'WW Day D')
from dual;
TO_CHAR(TO_DAT
--------------
01 Saturday 7

The Oracle always directly set first week on Jan-01, but day-of-week count
correct... It is pretty dirty, but it is a probably set in libc's mktime().

Well, we will in PG both version:

oracle's to_char:
* week-start is a sunday
* first week start on Jan-01, but day-of-week is count continual

PG date_part/trunc:
* week-start in monday
* first week is a first full week in new year (really?)

Karel