Re: Setting week starting day (was: Re: [GENERAL] Tabulate data incrementally)
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Setting week starting day (was: Re: [GENERAL] Tabulate data incrementally)
Richard Huxton 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 <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Tabulate data incrementally
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.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Tabulate data incrementally
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tabulate data incrementally
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
John1/1 100
Mary1/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 <[EMAIL PROTECTED]> 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/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Tabulate data incrementally
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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tabulate data incrementally
Omar Eljumaily <[EMAIL PROTECTED]> 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/
[GENERAL] 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.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
