It is not hard to calculate, as you can see... but it would be nice if
"date_trunc('week', date)" could do that directly. Even if it became
"date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')"
it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day
+ 1)
to have a one day offset from the standard first day of the week.
I believe there's more than that... Probably the "+1" should be outside
the
date_trunc, anyway. It might help, but I still see the need to to do
calculations... Specially if it was Tuesday today...
Out of curiosity, why does the database need to know this, or to be able to
calculate it? There are lots of things that would be useful to me, if the
RDBMS I'm using at the time supported them (particularly certain statistical
functions - ANOVA, MANOVA, nonlinear least squares regression, time series
analysis, &c.), but given that I can readily obtain these from other
software I use, and can if necessary put the requisite code in a middleware
component, I would rather have the PostgreSQL developer's focus on issues
central to having a good DB, such as ANSI standard compliance for SQL, or
robust pooling, &c. and just leave me a mechanism for calling functions that
are external to the database for the extra stuff I need. I would prefer a
suite of applications that each does one thing well than a single
application that does a mediocre job on everything it allegedly supports.
What would be 'nice' and what is practical are often very different things.
I know what you're after is simple, but remember the good folk responsible
for PostgreSQL have only finite time available to work on it, and thus, when
they're making choices about priorities, I'd rather they ignore even simple
ancillary stuff and focus on what really matters.
I just recently finished a project in which the data processing needed
information similar to what you're after, but instead of doing it in the
database, we opted to do it in the Perl script I wrote that fed data to the
database. In fact, it wasn't so much the day of the week that mattered to
the processing algorithm but the resulting dates for the immediately
preceding business day and the immediately following business day. It was
those dates we fed to the database rather than the weekday. There are
several Perl packages (see CPAN) supporting this kind of calculation. These
are generally outstanding (and would probably be useful if you want to
create your own stored function implemented in Perl), but you may have to
customize them by providing additional configuration information such as
timezone and statutory and religious holidays if you need to determine
business days in addition to just the day of the week. the day of the week
can be obtained in Perl with a single function call!
I just took a quick break to read about the date functions available within
PostgreSQL, and while apparently nice, you have much greater flexibility,
and many more functions, in these Perl packages I mentioned. If you just
want a function call, I'd suggest you create a function that just dispatches
a call to the Perl function that best meets your needs. In a sense, you are
not really rolling your own. You're just dispatching the call to a function
in a Perl package.
Cheers
Ted
