Re: 'GROUP BY' problem

Mariusz Czulada wrote:

> I'd love to do it this way:
>
> SELECT
> date_trunc('15 minutes',ts),
> min(cpu_busy_pct),
> avg(cpu_busy_pct),
> max(cpu_busy_pct)
> FROM
> tmp_server_perf_sum
> GROUP BY
> date_trunc('15 minutes',ts);

The best I can think of at the moment is:
SELECT

(trunc(date_part('epoch',ts::timestamptz)/900)*900)::int::abstime::timestamp
,
min(cpu_busy_pct),
avg(cpu_busy_pct),
max(cpu_busy_pct)
FROM tmp_server_perf_sum
GROUP BY 1;

i.e. convert to seconds since epoch, truncate to 900 seconds = 15 minutes,
and convert
back to a timestamp.

You could wrap this in a function such as:

create function trunc_quarter_hour(timestamptz) returns timestamp
language plpgsql immutable strict
as '
begin
return (trunc(date_part(''epoch'',$1)/900)*900)::int::abstime;
end
';

and then use:
SELECT trunc_quarter_hour(ts), <etc>

This would allow you to substitute a better calculation into the function
without
changing your queries.

--
Peter Gibbs
EmKel Systems