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.

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