Thread: 'GROUP BY' problem

Hi all,

I have a table:

CREATE TABLE tmp_server_perf_sum (
    ts timestamp without time zone,
    lock_count double precision,
    cpu_busy_pct double precision,
    transactions_rate double precision,
    deadlock_count double precision
);

/* data from Sybase Historical Server */

I can:

SELECT
    date_trunc('hour',ts),
    min(cpu_busy_pct),
    avg(cpu_busy_pct),
    max(cpu_busy_pct)
FROM
    tmp_server_perf_sum
GROUP BY
    date_trunc('hour',ts);

and it works great. But 1h interval is to wide for me. On the over hand 1
minute is less than my samples in table. I'd like to group results for every
15 minues. 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);

but unfortunately it is not working (I use 7.2.2, but IMHO it is not
important).

1. Can sb. help me with this query? How to make such groupping?

2. Do you think is it possible/needed to extend 'date_trunc' function to
support such options?

TIA

Mariusz Czulada

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