OK that worked, but now how do I get the integer returned by extract
into a human friendly string like
7/1 - 7/8 or something similar ?
On Jul 31, 2009, at 12:37 PM, Rob Wickert wrote:
> You would use some of postgresql built in date functions to extract
> the week from the date and group by that
>
> i.e.
>
> select carrier, extract(week from start_time) as week,
> sum(call_length) as totallength, sum(cost) as total_cost
> from mytable
> group by carrier, extract(week from start_time)
> order by week, carrier
>
> you'll probably want to extract the year as well so dates weeks from
> alternate years don't get merged together
>
> Ketema Harris wrote:
>> I have a table defined as
>> CREATE TABLE mytable
>> (
>> carrier varchar,
>> start_time timestamp with time zone,
>> call_date date,
>> cost numeric,
>> call_length numeric
>> )
>> 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 ?