Search Postgresql Archives

Re: Setting week starting day

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

 



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



[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