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 ?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
