Re: extract (dow/week from date)

On Sun, 21 Aug 2005, Clodoaldo Pinto wrote:

> 2005/8/21, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>:
> > On Sat, 20 Aug 2005, Tom Lane wrote:
> >
> > > Clodoaldo Pinto <clodoaldo(dot)pinto(at)gmail(dot)com> writes:
> > > > I'm ordering by date just to show that sunday, the 0th day of the
> > > > week, is the last day of a given week, which is not what I need.
> > >
> > > extract(week) follows the ISO definition of week, which is pretty
> > > strange anyway, but in particular it says that weeks start on Monday.
> > > extract(dow) follows a different convention. There's not a lot we
> > > can do about this --- we're certainly not going to change extract(week),
> > > and I can't see changing extract(dow) either.
> >
> > Instead of change the existing ones, couldn't we add a new extract format
> > for "iso day of week" that returns 1-7 for monday-sunday that would be
> > consistent with the week definition?
> >
> It would work for me. The problem is not if is sunday or monday the
> first day of the week, but to make all days of the week from extract
> (dow) (or a new extract (isodow)) fit into the same week from extract
> (week).

I think something like:
(CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
would give consistent results right now, but that's terribly ugly
(although could be hidden in a function). Of course, even if it were to
be added, it'd probably not make it until 8.2, so you'd probably need a
workaround in the meantime anyway.

> There is the same mismatch in to_char ('WW') related to to_char ('D')

I'd think you'd want to be comparing IW and D, but it'd presumably still
be broken since sunday is 1 rather than monday. Since that function is
supposed to emulate Oracle in alot of places IIRC, so it'd be interesting
to see what Oracle does on those dates.

> Of course it would be even better if we could pass parameters to the
> functions changing its behavior such as sunday/monday as the first day
> or 0-1 as the first day.

I don't personally see that as better, really.