Ted, my reason for asking the question that I believe precipitated this
thread was that I wanted a single sql statement that aggregated time
data by week. Yes, I could do the aggregation subsequently in my own
client side code, but it's easier and less error prone to have it done
by the server.
Ted Byers wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
