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

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