Thread: Aggregate time data on half hour interval

I am looking for a way to aggregate timestamped data on a half hour
interval, not just by the hour or minute. Suppose I have a table composed
of:

    Date   |   Time   | Data
-----------+----------+------
1999-12-19 | 10:00:00 | 76
1999-12-19 | 10:15:00 | 72
1999-12-19 | 10:30:00 | 77
1999-12-19 | 10:45:00 | 71
1999-12-19 | 11:00:00 | 74
1999-12-19 | 11:15:00 | 78

I can aggregate by the hour without problem:
SELECT Date, date_trunc('hour', Time) AS HOUR, SUM(Data)
FROM Table
GROUP BY Date, HOUR;

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?

Thanks in advance,

-Lars

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

> In the long run it seems like date_trunc ought to be more flexible
> than it is...

Now that we can easily overload built-in functions, we should be able to
have an alternate form for date_trunc() which can do this. Say, by
accepting a double value as the first argument, which would be the
amount to round, in seconds, like this:

  date_trunc(20.0, tsfield)

or by adding one more argument which would be the units, like this:

  date_trunc('minutes', 20.0, tsfield)

I think I like this second one better.

Or are you proposing something specific for the current date_trunc()?
The current form is modeled on the Ingres function of the same name.

                 - Thomas

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
>

If I wanted to aggregate a date field by week is the answer the same
or can date_trunc do that?  A quick check implies no date_trunc...

-Kyle


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