Thread: Tabulate data incrementally

I want to tabulate time data on a weekly basis, but my data is entered
on a daily basis.

create table time_data
{
    employee varchar(10),
    _date date,
    job varchar(10),
    amount
}

So I want to tabulate with a single sql command.  Is that possible?

If I had a separate week end table
create table week_ends
{
    end_date date
}

I could do something like.

select *, (select sum(amount) from time_data where _date > end_date - 7
and _data <= end_date) from week_ends;

 But the week_end table would be a pain to manage for a number of
reasons.  Is it possible to do this without the week_end table?

Thanks.

Omar Eljumaily <omar2@omnicode.com> writes:
> I want to tabulate time data on a weekly basis, but my data is entered
> on a daily basis.

Something involving GROUP BY date_trunc('week', _date) might work for
you, if your definition of week boundaries matches date_trunc's.
If not, you could probably make a custom function that breaks at the
boundaries you want.

            regards, tom lane

Omar Eljumaily wrote:
> I want to tabulate time data on a weekly basis, but my data is entered
> on a daily basis.
>
> create table time_data
> {
>    employee varchar(10),
>    _date date,
>    job varchar(10),
>    amount
> }
>
> So I want to tabulate with a single sql command.  Is that possible?

Try one of these:

=> SELECT date_trunc('week',now());
        date_trunc
------------------------
  2007-03-05 00:00:00+00

=> SELECT extract(week from now());
  date_part
-----------
         10


--
   Richard Huxton
   Archonet Ltd

Thanks Tom and Richard for the tip on date_trunc.  Is it possible in an
sql select statement to create an iterator?

For instance

select myItFunc(1,10);

would give 1,2,3,4,5,6,7,8,9,10

I'm a bit embarrassed that I don't know how to do this.  My
understanding of sql functions is that not being object oriented, they
don't store state.

The reason I'm asking is that if I wanted to to use date_trunc, I think
I would need some sort of iterator to get multiple rows in one statement.

What I'm looking for is:

Employee Week   Amount
John            1/1      100
Mary            1/1     0
Edward      1/2      100
etc

I'd also like to return zero or null values when the data doesn't
exist.  Wouldn't I need an iterator to do that?

Thanks,

Omar


Tom Lane wrote:
> Omar Eljumaily <omar2@omnicode.com> writes:
>
>> I want to tabulate time data on a weekly basis, but my data is entered
>> on a daily basis.
>>
>
> Something involving GROUP BY date_trunc('week', _date) might work for
> you, if your definition of week boundaries matches date_trunc's.
> If not, you could probably make a custom function that breaks at the
> boundaries you want.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

Omar Eljumaily wrote:
> Thanks Tom and Richard for the tip on date_trunc.  Is it possible in an
> sql select statement to create an iterator?

Yes, use the generate_series() function.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thanks Alvaro.  That's good to know.  Actually I was spacing on the need
for this.  The date_trunc function with group by actually works for me.

select sum(amount), date_trunc('week', period_end) as dt from time_data
group by dt;


Alvaro Herrera wrote:
> Omar Eljumaily wrote:
>
>> Thanks Tom and Richard for the tip on date_trunc.  Is it possible in an
>> sql select statement to create an iterator?
>>
>
> Yes, use the generate_series() function.
>
>

Richard Huxton <dev@archonet.com> writes:

> Omar Eljumaily wrote:
>> I want to tabulate time data on a weekly basis, but my data is entered on a
>> daily basis.
>>
>> create table time_data
>> {
>>    employee varchar(10),
>>    _date date,
>>    job varchar(10),
>>    amount
>> }
>>
>> So I want to tabulate with a single sql command.  Is that possible?
>
> Try one of these:
>
> => SELECT date_trunc('week',now());
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00+00
>
> => SELECT extract(week from now());
>  date_part
> -----------
>         10


Hi!


I'm hijacking this thread a bit...  Is it possible to specify dinamically the
day of the week when week starts?

I mean, if I wanted to do the above but instead of Sunday or Monday as the
starting day I'd like using Fridays or Wednesdays...

Is it possible?  Writing a new function shouldn't be too hard -- it's a matter
of truncating the week on a day and shifting the date forward or backward --,
but something like a "SET bow=5" (to make the API consistent with the 'dow'
that already exists) would be really great!


Why doing that?  Imagine an accounting office where all their activities
should be closed and values summed up every Wednesday.  Or a company that
tracks the end of their activies weekly and consider the end of the week on
Thursdays (so that they can send invoices on Friday).

Being able to count "the first day of the 'week' 5 weeks from now" for the
above situations would make things easier to code. :-)


--
Jorge Godoy      <jgodoy@gmail.com>

Jorge Godoy escribió:

> I mean, if I wanted to do the above but instead of Sunday or Monday as the
> starting day I'd like using Fridays or Wednesdays...
>
> Is it possible?  Writing a new function shouldn't be too hard -- it's a matter
> of truncating the week on a day and shifting the date forward or backward --,
> but something like a "SET bow=5" (to make the API consistent with the 'dow'
> that already exists) would be really great!

Is it not just a matter of adding a constant and then taking modulo 7?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

> Jorge Godoy escribió:
>
>> I mean, if I wanted to do the above but instead of Sunday or Monday as the
>> starting day I'd like using Fridays or Wednesdays...
>>
>> Is it possible?  Writing a new function shouldn't be too hard -- it's a matter
>> of truncating the week on a day and shifting the date forward or backward --,
>> but something like a "SET bow=5" (to make the API consistent with the 'dow'
>> that already exists) would be really great!
>
> Is it not just a matter of adding a constant and then taking modulo 7?

As I said, it is easy with a function. :-)  I was just curious to see if we
had something like Oracle's NEXT_DAY function or something like what I
described (SET BOW=4; -- makes Thursday the first day of week):

================================================================================
NEXT_DAY

Syntax


Purpose
Returns the date of the first weekday named by char that is later than the
date d. The argument char must be a day of the week in the date language of
your session, either the full name or the abbreviation. The minimum number of
letters required is the number of letters in the abbreviated version. Any
characters immediately following the valid abbreviation are ignored. The
return value has the same hours, minutes, and seconds component as the
argument d.


Example

This example returns the date of the next Tuesday after March 15, 1998.

SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY"
     FROM DUAL;

NEXT DAY
---------

16-MAR-98
================================================================================


So, I'd have something like: "SELECT NEXT_DAY(now()+'5 weeks'::INTERVAL,
'THURSDAY');" to give me the next Thursday 5 weeks from now.


Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

On Thu, Mar 08, 2007 at 20:32:22 -0300,
  Jorge Godoy <jgodoy@gmail.com> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>
> As I said, it is easy with a function. :-)  I was just curious to see if we
> had something like Oracle's NEXT_DAY function or something like what I
> described (SET BOW=4; -- makes Thursday the first day of week):

If you are actually using "date" you can get the effect you want by adding
a constant integer to the date in the date_trunc function. That seems
pretty easy.

Bruno Wolff III <bruno@wolff.to> writes:

> On Thu, Mar 08, 2007 at 20:32:22 -0300,
>   Jorge Godoy <jgodoy@gmail.com> wrote:
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>
>> As I said, it is easy with a function. :-)  I was just curious to see if we
>> had something like Oracle's NEXT_DAY function or something like what I
>> described (SET BOW=4; -- makes Thursday the first day of week):
>
> If you are actually using "date" you can get the effect you want by adding
> a constant integer to the date in the date_trunc function. That seems
> pretty easy.


I couldn't see where to specify that integer.  Or, if it to sum it up to the
date, something that calculates it automatically.

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Adding an integer I'd still have to write the verifications (such as the one I
mention below for Oracle's NEXT_DATE()) to get the desired result.


Just to repeat my question:

(I don't want to write a function, I can do that pretty easily...  And I was
asking if there existed some feature on the database that...  It's just a
curiosity)

  Given a date X it would return me the first day of the week so that I can
  make this first day an arbitrary day, e.g. Friday or Wednesday.


Oracle's NEXT_DAY() gets closer to that, but would still require a few
operations (checking if the returned date is before the given date or if after
then subtract one week from this returned value, kind of a
"PREVIOUS_DATE()"...).


With a function I could make it easily, but then I'd have to wrap all
calculations with that...  It was just something to make life easier.  From
the answers I'm getting I see that there's no way to do that without a
function and that I'm not missing any feature on PG with regards to that ;-)

--
Jorge Godoy      <jgodoy@gmail.com>

Jorge Godoy escribió:

> Just to repeat my question:
>
> (I don't want to write a function, I can do that pretty easily...  And I was
> asking if there existed some feature on the database that...  It's just a
> curiosity)
>
>   Given a date X it would return me the first day of the week so that I can
>   make this first day an arbitrary day, e.g. Friday or Wednesday.

When you say "it would return", what's the "it"?

I wasn't proposing to use any function, just putting a simple expression
in the SELECT's result list (and maybe the GROUP BY, etc).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I think you can coax the date_trunc function to give you a proper start
day.  I think it's more than adding an integer to your date, though.
You also have to do some mod work after the function returns, I think.
I agree that the point isn't that you can't do it with some effort,
however.  It's mainly that it's a bit linguistically unintuitive.  It
would be nice to have a start date as an argument to the function.

Having said that, my own personal use of it will definitely be inside
another "wrapper" function because I need database platform
independence, so I need to abstract the function to look the same on all
of my platforms.


Jorge Godoy wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
>
>> On Thu, Mar 08, 2007 at 20:32:22 -0300,
>>   Jorge Godoy <jgodoy@gmail.com> wrote:
>>
>>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>>
>>> As I said, it is easy with a function. :-)  I was just curious to see if we
>>> had something like Oracle's NEXT_DAY function or something like what I
>>> described (SET BOW=4; -- makes Thursday the first day of week):
>>>
>> If you are actually using "date" you can get the effect you want by adding
>> a constant integer to the date in the date_trunc function. That seems
>> pretty easy.
>>
>
>
> I couldn't see where to specify that integer.  Or, if it to sum it up to the
> date, something that calculates it automatically.
>
> http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> Adding an integer I'd still have to write the verifications (such as the one I
> mention below for Oracle's NEXT_DATE()) to get the desired result.
>
>
> Just to repeat my question:
>
> (I don't want to write a function, I can do that pretty easily...  And I was
> asking if there existed some feature on the database that...  It's just a
> curiosity)
>
>   Given a date X it would return me the first day of the week so that I can
>   make this first day an arbitrary day, e.g. Friday or Wednesday.
>
>
> Oracle's NEXT_DAY() gets closer to that, but would still require a few
> operations (checking if the returned date is before the given date or if after
> then subtract one week from this returned value, kind of a
> "PREVIOUS_DATE()"...).
>
>
> With a function I could make it easily, but then I'd have to wrap all
> calculations with that...  It was just something to make life easier.  From
> the answers I'm getting I see that there's no way to do that without a
> function and that I'm not missing any feature on PG with regards to that ;-)
>
>

Alvaro Herrera <alvherre@commandprompt.com> writes:

> Jorge Godoy escribió:
>
>> Just to repeat my question:
>>
>> (I don't want to write a function, I can do that pretty easily...  And I was
>> asking if there existed some feature on the database that...  It's just a
>> curiosity)
>>
>>   Given a date X it would return me the first day of the week so that I can
>>   make this first day an arbitrary day, e.g. Friday or Wednesday.
>
> When you say "it would return", what's the "it"?

The function that came with the database, the feature, the something. :-)

> I wasn't proposing to use any function, just putting a simple expression
> in the SELECT's result list (and maybe the GROUP BY, etc).

So I'm blind on how to do that.  Maybe some "CASE"?


Here's what I was asking for (Sunday=0, Saturday=6, to remember ;-)):

================================================================================
testdb=# select current_date;
    date
------------
 2007-03-09
(1 row)

testdb=# select current_date + '3 weeks'::interval;
      ?column?
---------------------
 2007-03-30 00:00:00
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval);
     date_trunc
---------------------
 2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', date_trunc('week', current_date + '3 weeks'::interval));
 date_part
-----------
         1
(1 row)

testdb=#
================================================================================


This is the standard behavior.  It returns me the first monday.  Now, if I had
the week starting on Wednesdays, I should get 2007-03-28 instead of
2007-03-26.

I can check in a function to see if the returned date is before or after my
desired week-start-day (as in Wednesdays, for example) and if date_part('dow',
date) is bigger than it return the value for Monday + 2 days, if it is lower
then return Monday - 5 days.

For example, again:

================================================================================
testdb=# select date_part('dow', current_date + '3 weeks'::interval);
 date_part
-----------
         5
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval) + '2 days'::interval;
      ?column?
---------------------
 2007-03-28 00:00:00
(1 row)

testdb=#
================================================================================

That would be the "first day" of the week in three weeks from now, with weeks
starting on Wednesdays.

If I had asked for this 3 days ago:

================================================================================
testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 weeks'::interval);
     date_trunc
---------------------
 2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', current_date - '3 days'::interval + '3 weeks'::interval);
 date_part
-----------
         3
(1 row)

testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 weeks'::interval) - '5 days'::interval;
      ?column?
---------------------
 2007-03-21 00:00:00
(1 row)

testdb=#
================================================================================

Then if it was Tuesday, the week three weeks from now would have started on
Wednesday, 2007-03-21.


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 ;-)



Thanks for your attention, Alvaro. :-)


--
Jorge Godoy      <jgodoy@gmail.com>

On Fri, Mar 09, 2007 at 14:59:35 -0300,
  Jorge Godoy <jgodoy@gmail.com> 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.

Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Mar 09, 2007 at 14:59:35 -0300,
>   Jorge Godoy <jgodoy@gmail.com> 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...



neo=# select date_trunc('dow', current_date + 1);
ERRO:  unidades de timestamp with time zone "dow" são desconhecidas
neo=# select date_part('dow', current_date + 1);
 date_part
-----------
         6
(1 row)

neo=# select date_trunc('week', current_date + 1);
       date_trunc
------------------------
 2007-03-05 00:00:00-03
(1 row)

neo=# select date_trunc('week', current_date);
       date_trunc
------------------------
 2007-03-05 00:00:00-03
(1 row)

neo=#




--
Jorge Godoy      <jgodoy@gmail.com>

On Fri, Mar 09, 2007 at 16:44:57 -0300,
  Jorge Godoy <jgodoy@gmail.com> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > On Fri, Mar 09, 2007 at 14:59:35 -0300,
> >   Jorge Godoy <jgodoy@gmail.com> 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...

No, it has to be inside the function so that the modular arithmetic is
applied to it.

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

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

----- Original Message -----
From: "Omar Eljumaily" <omar2@omnicode.com>
To: "Ted Byers" <r.ted.byers@rogers.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 09, 2007 5:00 PM
Subject: Re: [GENERAL] Setting week starting day


> 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.
>
I  don't buy the suggestion that server side code is less error prone that
client side code, but be that as it may, we're talking about a function that
has one line of code.  And given what you just said, you don't want the day
of the week, you want a function that returns the week of the year.  This
can be had from the same Perl functions I mentioned before, with a minor
alteration in how you call it.  my suggestion would be to create that one
line function that invokes the relevant Perl function, which can then be
invoked in your select statement (presumably with a group clause to avoid
mixing data from different years).  It should take about ten to fifteen
minutes to write and test?

Ted

Bruno Wolff III <bruno@wolff.to> writes:

> No, it has to be inside the function so that the modular arithmetic is
> applied to it.

Then there's the error I've shown from your command.  Can you give me a
working one?  This was with PostgreSQL 8.2.3.

--
Jorge Godoy      <jgodoy@gmail.com>

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

Omar Eljumaily <omar2@omnicode.com> writes:

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

If you work closer to the data you have more efficiency.  To do what you want
you can write a function using plpgsql -- I've posted some ideas -- and that
is not hard at all.

But if there was something to make it easier to write this it would be great ;-)

--
Jorge Godoy      <jgodoy@gmail.com>

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

> I  don't buy the suggestion that server side code is less error prone that
> client side code, but be that as it may, we're talking about a function that
> has one line of code.  And given what you just said, you don't want the day of
> the week, you want a function that returns the week of the year.  This can be
> had from the same Perl functions I mentioned before, with a minor alteration
> in how you call it.  my suggestion would be to create that one line function
> that invokes the relevant Perl function, which can then be invoked in your
> select statement (presumably with a group clause to avoid mixing data from
> different years).  It should take about ten to fifteen minutes to write and
> test?


There's no need to use Perl.

neo=# select extract('week' from now());
 date_part
-----------
        10
(1 registro)

neo=#


Today is a day at the tenth week of the year.




--
Jorge Godoy      <jgodoy@gmail.com>

On Fri, Mar 09, 2007 at 20:13:11 -0300,
  Jorge Godoy <jgodoy@gmail.com> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > No, it has to be inside the function so that the modular arithmetic is
> > applied to it.
>
> Then there's the error I've shown from your command.  Can you give me a
> working one?  This was with PostgreSQL 8.2.3.

postgres=# select date_trunc('week', current_date + 1);
       date_trunc
------------------------
 2007-03-05 00:00:00-06
(1 row)

It turns out DOW isn't available for date_trunc. You can probably use
extract to get what you want. You probably should check that it works
at DST transitions, since the date value is cast to a timestamp and
if DST transitions happen at 0000 in your time zone, you might get an
unexpected answer.

postgres=# select extract(dow from current_date + 1);
 date_part
-----------
         6
(1 row)

Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Mar 09, 2007 at 20:13:11 -0300,
>   Jorge Godoy <jgodoy@gmail.com> wrote:
>> Bruno Wolff III <bruno@wolff.to> writes:
>>
>> > No, it has to be inside the function so that the modular arithmetic is
>> > applied to it.
>>
>> Then there's the error I've shown from your command.  Can you give me a
>> working one?  This was with PostgreSQL 8.2.3.
>
> postgres=# select date_trunc('week', current_date + 1);
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00-06
> (1 row)
>
> It turns out DOW isn't available for date_trunc. You can probably use
> extract to get what you want. You probably should check that it works
> at DST transitions, since the date value is cast to a timestamp and
> if DST transitions happen at 0000 in your time zone, you might get an
> unexpected answer.
>
> postgres=# select extract(dow from current_date + 1);
>  date_part
> -----------
>          6
> (1 row)

But how to get the date if the first day of the week is a Wednesday?  This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result.

--
Jorge Godoy      <jgodoy@gmail.com>

On Fri, Mar 09, 2007 at 23:07:26 -0300,
  Jorge Godoy <jgodoy@gmail.com> wrote:
>
> But how to get the date if the first day of the week is a Wednesday?  This
> example is like the ones I've sent with separate queries that needed being
> combined -- in a function, probably -- to get the desired result.

If you want to group on weeks that start on Wednesdays add 5.

postgres=# select date_trunc('week', '2007-03-07'::date + 5);
       date_trunc
------------------------
 2007-03-12 00:00:00-05
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5);
       date_trunc
------------------------
 2007-03-05 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-08'::date + 5);
       date_trunc
------------------------
 2007-03-12 00:00:00-05
(1 row)

But you're always returning Monday, right?  Your grouping will be
correct, but to get the actual truncation date, you have to subtract back.

select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week', '2007-03-08'::date + 5)::date-5);


Bruno Wolff III wrote:
> On Fri, Mar 09, 2007 at 23:07:26 -0300,
>   Jorge Godoy <jgodoy@gmail.com> wrote:
>
>> But how to get the date if the first day of the week is a Wednesday?  This
>> example is like the ones I've sent with separate queries that needed being
>> combined -- in a function, probably -- to get the desired result.
>>
>
> If you want to group on weeks that start on Wednesdays add 5.
>
> postgres=# select date_trunc('week', '2007-03-07'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)
>
> postgres=# select date_trunc('week', '2007-03-06'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00-06
> (1 row)
>
> postgres=# select date_trunc('week', '2007-03-08'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Mar 09, 2007 at 23:07:26 -0300,
>   Jorge Godoy <jgodoy@gmail.com> wrote:
>>
>> But how to get the date if the first day of the week is a Wednesday?  This
>> example is like the ones I've sent with separate queries that needed being
>> combined -- in a function, probably -- to get the desired result.
>
> If you want to group on weeks that start on Wednesdays add 5.

I believe you either missed my post with several queries showing what I wanted
or you didn't understand the point.

If I run this query:

   select date_trunc('week', '2007-03-08'::date + 5);

it fails even for that date.  The correct answer, would be 2007-03-07 and not
2007-03-12.  I want the first day of the week to be Wednesday and hence I want
the Wednesday for the week the date is in.  (Wednesday was arbitrarily chosen,
it could be Thursday, Tuesday, Friday, etc.)


> postgres=# select date_trunc('week', '2007-03-07'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)

This should be 2007-03-07 since 2007-03-07 *is* a Wednesday and that's when
the week starts.

> postgres=# select date_trunc('week', '2007-03-06'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-05 00:00:00-06
> (1 row)

This should be 2007-02-28 since this is the first day of the week for the week
that starts on Wednesday 2007-02-28 and ends on 2007-03-06.

> postgres=# select date_trunc('week', '2007-03-08'::date + 5);
>        date_trunc
> ------------------------
>  2007-03-12 00:00:00-05
> (1 row)

This should return the same date as the first query (2007-03-07).  2007-03-12
is a Monday, and weeks should always start on Wednesday on my arbitrary
question.


This is why I can't envision a simple query for that but it is easy with a
function.

Again, the function should do something like:

       - make the date calculation (e.g. add some interval or nothing at all...)

       - get the resulting 'dow'

       - if it is > than the arbitrary day that was determined to be the first
         day of the week (Wednesday on my example), then return
         date_trunc('week') + 2 days (2 for moving from Monday to Wednesday,
         for different first days the shift should be different)

       - if it is < than the arbitrary day that was determined to be the first
         day of the week (Wednesday, again), then return date_trunc('week') -
         5 days (-5 for moving from Monday to the previous Wednesday)


The result when asked for the first day should always be the Wednesday that is
equal to the date or that ocurred right before it.  It is the same idea that
is implemented today that returns Monday, but instead of Monday I want another
day that in my posts happened to be exemplified by Wednesday.



I don't want you to expend your time.  It was just a question that got
answered indirectly with a "there's no way to do that without using a
function" due to the complexity above and the lack of such feature in
PostgreSQL.  It is simple to have it as a function, though.

I don't know any RDBMS that implements that.  All of them require some
operations to get the desired result.



Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

Omar Eljumaily <omar2@omnicode.com> writes:

> But you're always returning Monday, right?  Your grouping will be correct, but
> to get the actual truncation date, you have to subtract back.
>
> select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
> select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
> select (date_trunc('week', '2007-03-08'::date + 5)::date-5);

Indeed.  This gives the correct result.  So, we can change '5' for:

     7 - ('dow desired' - 1)

Replacing the above queries, then:

# select (date_trunc('week', '2007-03-07'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-03-07
(1 row)

# select (date_trunc('week', '2007-03-06'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-02-28
(1 row)

# select (date_trunc('week', '2007-03-08'::date + (7 - (3 - 1))))::date - (7 - (3 - 1));
  ?column?
------------
 2007-03-07
(1 row)



Parameterizing the desired day shouldn't be hard. ;-)


We subtract one from the desired day because PostgreSQL returns '1' for the
date_part('week') considering Mondays as the first day of the week.



Thanks, Omar.  This makes the function easier to write.  I hope it also solves
your problem.



Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

On Sat, Mar 10, 2007 at 00:03:04 -0300,
  Jorge Godoy <jgodoy@gmail.com> wrote:
>
> If I run this query:
>
>    select date_trunc('week', '2007-03-08'::date + 5);
>
> it fails even for that date.  The correct answer, would be 2007-03-07 and not
> 2007-03-12.  I want the first day of the week to be Wednesday and hence I want
> the Wednesday for the week the date is in.  (Wednesday was arbitrarily chosen,
> it could be Thursday, Tuesday, Friday, etc.)

If for some reason you actually need to display the date of the first day
of the week, rather than just group by it, then subtract the number of
days that were added inside, on the outside. Because date_trunc returns
a timestamp with timezone, you need to subtract an interval (or cast
back to date and subtract an integer). If you are getting the '5' from
somewhere hard coded you might want to use (5 * '1 day'::interval) rather
than '5 days'::interval .

So you would use:
select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;

postgres=# select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;
        ?column?
------------------------
 2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-07'::date + 5) - '5 days'::interval;
        ?column?
------------------------
 2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5) - '5 days'::interval;
        ?column?
------------------------
 2007-02-28 00:00:00-06
(1 row)