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
