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 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
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
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:
Show quoted text
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?
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:
Show quoted text
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>
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:
Show quoted text
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 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.
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:
Show quoted text
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