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