Re: Setting week starting day - Mailing list pgsql-general

"Ted Byers" <r.ted.byers@rogers.com> writes:

> 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

It was a curiosity.  But it would make working with some dates easier.  I've
given some examples but if you really want I may search for the messages and
repost them for you.

> 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

You can have those using R and plR inside the database. ;-)

> 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.

If I have to do calculations with dates inside the database the worst thing
I'd like to do was retrieving part of it, going to some external code, coming
back to the database and so on.

If there was something inside the database then I'd really like to know and
use it.  I don't see how worse it would be when compared to other non-ANSI
extensions that are already available.

> 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

There's no feeding here.  Imagine that I'm filtering huge selects to be
processed externaly.  I wouldn't like to get some millions of rows instead of
hundreds or a few thousands of them.

> 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

I know Perl.  I have already thought it for IBM... ;-)

> 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!

As in several other languages.  Even in plpgsql, with simple calculations like
I've shown.  As I said, writing a function for that is simple enough and I
just wanted to know if there was anything that could be done by the database.

I never asked for any new implementation.

> 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.

And to do that you have to write a function...


--
Jorge Godoy      <jgodoy@gmail.com>