On Fri, Jul 31, 2009 at 12:09:51PM -0400, Ketema Harris wrote:
> I want to create a query that will generate a the following columns:
>
> carrier, week, sum(call_length) as totallength, sum(cost) as total_cost
> from mytable
> group by carrier, (WHAT HERE?)
> order by week, carrier
>
> week is defined as a date range so something like 7/6/2009 - 7/13/2009
>
> I would need the timestamps to be grouped into 7 day intervals
> starting from the first one and moving through the table. is this
> possible in a single query or would I have to write a function ?
Either use something like date_trunc[1], convert it to a string with
to_char[2], or create a table that contains what you consider to be your
week ranges in (i.e. year, week, startdate, enddate).
--
Sam http://samason.me.uk/
[1] http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
[2] http://www.postgresql.org/docs/current/static/functions-formatting.html