Search Postgresql Archives

Re: extract (dow/week from date)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, 21 Aug 2005, Clodoaldo Pinto wrote:

> 2005/8/21, Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx>:
> > On Sat, 20 Aug 2005, Tom Lane wrote:
> >
> > > Clodoaldo Pinto <clodoaldo.pinto@xxxxxxxxx> 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux