

We have for same questions different answers now:

(DAY OF WEEK)

test=# select date_part('dow', now());
 date_part
-----------
         1
(1 row)

test=# select to_char(now(), 'D');
 to_char
---------
 2
(1 row)


For to_char() I use POSIX definition of 'tm' where week start on Sunday.
 
Is it right? (Exuse me, I see archive, but without some effect...).

Or we will support both styles?

						Karel




Mon, 5 Jun 2000 22:52:51 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
day of week
At 10:52 PM 6/5/00 +0200, Karel Zak wrote:
>
>
>We have for same questions different answers now:
>
>(DAY OF WEEK)
>
>test=# select date_part('dow', now());
> date_part
>-----------
>         1
>(1 row)
>
>test=# select to_char(now(), 'D');
> to_char
>---------
> 2
>(1 row)
>
>
>For to_char() I use POSIX definition of 'tm' where week start on Sunday.
> 
>Is it right? (Exuse me, I see archive, but without some effect...).
>
>Or we will support both styles?

to_char() gives the same answer with Oracle, as it is supposed to
and as you intended it to.

I personally don't find it all that disconcerting that the two give
different answers.  Change the old, PG way and lots of old code
is likely to break.  Change to_char() and the desired compatibility
with Oracle breaks.

I think it boils down to needing good documentation ???



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 05 Jun 2000 14:03:46 -0700
Don Baccus <dhogaza@pacifier.com>
Re: day of week

> >For to_char() I use POSIX definition of 'tm' where week start on Sunday.
> > 
> >Is it right? (Exuse me, I see archive, but without some effect...).
> >
> >Or we will support both styles?
> 
> to_char() gives the same answer with Oracle, as it is supposed to
> and as you intended it to.
> 
> I personally don't find it all that disconcerting that the two give
> different answers.  Change the old, PG way and lots of old code
> is likely to break.  Change to_char() and the desired compatibility
> with Oracle breaks.

 It is a Solomon's answer :-), but well. I agree. 

> I think it boils down to needing good documentation ???

 OK, I add it to to_char() and to date_[ trunc | dart ].


I'm just now working on 'week' support to date_trunc().

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 ?

 Comments?

(I vote for 'sunday' like first day.)
 
					Karel




Mon, 5 Jun 2000 23:34:29 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: day of week
Don Baccus <dhogaza@pacifier.com> writes:
> At 10:52 PM 6/5/00 +0200, Karel Zak wrote:
>> For to_char() I use POSIX definition of 'tm' where week start on Sunday.
>> 
>> Is it right? (Exuse me, I see archive, but without some effect...).
>> 
>> Or we will support both styles?

> to_char() gives the same answer with Oracle, as it is supposed to
> and as you intended it to.

I don't think we should change to_char(), but it might make sense
to create a SET variable that controls the start-of-week day for
date_part(); or just have several variants of 'dow' for different
start-of-week days.  Different applications might reasonably want
different answers depending on what conventions they have to deal
with outside of Postgres.

Thomas Lockhart is usually our lead guy on datetime-related issues.
Let's see what he thinks when he gets back from vacation (he's gone
till next week IIRC).

			regards, tom lane

Mon, 05 Jun 2000 20:30:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: day of week 
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.

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


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 6 Jun 2000 18:07:52 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
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


Tue, 6 Jun 2000 19:06:00 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: day of week
Karel Zak writes:

> 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().

The first week of the year is most certainly not (always) the week with
Jan-01 in it. My understanding is that it's the first week where the
Thursday is in the new year, but I might be mistaken. Here in Sweden much
of the calendaring is done based on the week of the year concept, so I'm
pretty sure that there's some sort of standard on this. And sure enough,
this year started on a Saturday, but according to the calendars that hang
around here the first week of the year started on the 3rd of January.


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

The worst thing we could do is having an inconsistency here. Having a
configuration option or two that applies to both sounds better.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Wed, 7 Jun 2000 18:35:49 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: day of week

On Wed, 7 Jun 2000, Peter Eisentraut wrote:

> Karel Zak writes:
> 
> > 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().
> 
> The first week of the year is most certainly not (always) the week with
> Jan-01 in it. My understanding is that it's the first week where the
> Thursday is in the new year, but I might be mistaken. Here in Sweden much
> of the calendaring is done based on the week of the year concept, so I'm
> pretty sure that there's some sort of standard on this. And sure enough,
> this year started on a Saturday, but according to the calendars that hang
> around here the first week of the year started on the 3rd of January.

 You probably right. I belive that Thomas say more about it...

> > 
> > 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?)  
> 
> The worst thing we could do is having an inconsistency here. Having a
> configuration option or two that applies to both sounds better.

 Yes, but Oracle "porters" need probably oracle pseudo 
calculation..

 For PG date_part/trunc will SET (or anything like this) good.

							Karel


Wed, 7 Jun 2000 18:56:42 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: day of week
>  You probably right. I belive that Thomas say more about it...

to_char() is compatible with Oracle. date_part() is compatible with
Ingres (or should be). I've got the docs somewhere, but presumably I
looked at them when implementing this in the first place. Maybe not;
what I have is compatible with Unix date formatting.

>  For PG date_part/trunc will SET (or anything like this) good.

Let's decide what these functions are for; in this case they are each
cribbed from an existing database product, and should be compatible with
those products imho.

btw, the "week of year" issue is quite a bit more complex; it is defined
in ISO-8601 and it does not correspond directly to a "Jan 1" point in
the calendar.

                    - Thomas

Tue, 13 Jun 2000 02:27:37 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: day of week