Re: Aggregate time data on half hour interval - Mailing list pgsql-general

It may be ugly, but it sure works! Here is my query:

SELECT Date, interval(trunc(date_part('epoch', time)/1800) * 1800) AS
HALFHOUR, SUM(Data)
FROM Table
GROUP BY Date, HALFHOUR;

This seems to work great, and I don't see any performance hit either.
Thanks,

-Lars

On Thu, 20 Jul 2000, Tom Lane wrote:

> Lars <lars@sscsinc.com> writes:
> > What I would like to do though is aggregate by each half hour or maybe
> > even 20 minutes. Does anyone know a good way to do this?
>
> I don't have a *good* answer, but a brute-force way is
>
> * convert timestamp to integer seconds with date_part('epoch', foo)
> * round to a multiple of desired time interval
> * convert back to timestamp for display via timestamp()
>
> In the long run it seems like date_trunc ought to be more flexible
> than it is...
>
>             regards, tom lane
>