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
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:
Show quoted text
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
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:
Show quoted text
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