Thread: day of week
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
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
Serviceand other goodies at http://donb.photo.net.
> >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
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
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
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
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
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
> 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. In Sweden (and several other places), "Week 1" is defined as "the first week that has at least four days in the new year". While it's not an authority, my MS Outlook Calendar allows me to chose from: "Starts on Jan 1", "First 4-day week" and "First full week". So it would seem there are at least these three possibilities. //Magnus
> 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
> 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 Ingres docs somewhere, but
presumably I looked at them when implementing this in the first place.
Maybe not, but 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. In fact, there can be 53 weeks in a year, and some days
early in the calendar year will fall into the preceeding year for
purposes of this week calculation.
- Thomas