pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery to return hourly snapshot
3+ messages / 2 participants
[nested] [flat]
* query to return hourly snapshot
@ 2006-04-05 00:58 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Broersma Jr @ 2006-04-05 00:58 UTC (permalink / raw)
To: pgsql-sql
I orginally sent this email to the [novice] list but did not get any response.
I am look for help in developing a query that will return the nearest process record that was
logged at or after each hour in a day (i.e. hourly snapshot).
Below are typical sample data. Also, I've included a query used to return the average of the
process value for each hour to give an example for the format that I am trying to achieve.
Thanks for the help.
Regards,
Richard
select * from process limit 10;
tstamp | process
---------------------+---------
2005-10-25 21:10:41 | 3.56513
2005-10-25 21:10:42 | 3.56503
2005-10-25 21:10:43 | 3.56494
2005-10-25 21:10:44 | 3.56484
2005-10-25 21:10:45 | 3.56475
2005-10-25 21:10:46 | 3.56465
2005-10-25 21:10:47 | 3.56455
2005-10-25 21:10:48 | 3.56446
2005-10-25 21:10:49 | 3.56436
2005-10-25 21:10:50 | 3.56427
(10 rows)
select avg(process),
date_trunc('hour',tstamp) as date
from process
where date_trunc('day', tstamp) = '2005-10-26'
group by date_trunc('hour', tstamp)
order by date_trunc('hour', tstamp);
avg | date
------------------+---------------------
9.79195118032606 | 2005-10-26 05:00:00
10.0249767947376 | 2005-10-26 06:00:00
8.88596018049452 | 2005-10-26 07:00:00
7.95090951088542 | 2005-10-26 08:00:00
8.10741349776586 | 2005-10-26 09:00:00
7.30079822791947 | 2005-10-26 10:00:00
7.10586501293712 | 2005-10-26 11:00:00
8.15196838166979 | 2005-10-26 12:00:00
8.26183129151662 | 2005-10-26 13:00:00
8.95141531440947 | 2005-10-26 14:00:00
10.562882253329 | 2005-10-26 15:00:00
10.8634908244445 | 2005-10-26 16:00:00
11.4077104069976 | 2005-10-26 17:00:00
12.4702264580744 | 2005-10-26 18:00:00
11.9155618293134 | 2005-10-26 19:00:00
11.5622152555012 | 2005-10-26 20:00:00
11.6527367563489 | 2005-10-26 21:00:00
10.3170960432442 | 2005-10-26 22:00:00
9.56747980806563 | 2005-10-26 23:00:00
(19 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query to return hourly snapshot
@ 2006-04-05 02:49 Michael Fuhr <mike@fuhr.org>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-04-05 02:49 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> I am look for help in developing a query that will return the nearest
> process record that was logged at or after each hour in a day (i.e.
> hourly snapshot).
Are you looking for something like this?
SELECT p.process, date_trunc('hour', p.tstamp) AS hour
FROM process AS p
JOIN (
SELECT date_trunc('hour', tstamp), min(tstamp)
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
GROUP BY date_trunc('hour', tstamp)
) AS s ON s.min = p.tstamp
ORDER BY hour;
Or, using PostgreSQL's non-standard DISTINCT ON clause:
SELECT DISTINCT ON (date_trunc('hour', tstamp))
process, date_trunc('hour', tstamp) AS hour
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
ORDER BY date_trunc('hour', tstamp), tstamp;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query to return hourly snapshot
@ 2006-04-05 04:37 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Broersma Jr @ 2006-04-05 04:37 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Yes! Thanks you very much!
--- Michael Fuhr <mike@fuhr.org> wrote:
> On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> > I am look for help in developing a query that will return the nearest
> > process record that was logged at or after each hour in a day (i.e.
> > hourly snapshot).
>
> Are you looking for something like this?
>
> SELECT p.process, date_trunc('hour', p.tstamp) AS hour
> FROM process AS p
> JOIN (
> SELECT date_trunc('hour', tstamp), min(tstamp)
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> GROUP BY date_trunc('hour', tstamp)
> ) AS s ON s.min = p.tstamp
> ORDER BY hour;
>
> Or, using PostgreSQL's non-standard DISTINCT ON clause:
>
> SELECT DISTINCT ON (date_trunc('hour', tstamp))
> process, date_trunc('hour', tstamp) AS hour
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> ORDER BY date_trunc('hour', tstamp), tstamp;
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-05 04:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-05 00:58 query to return hourly snapshot Richard Broersma Jr <rabroersma@yahoo.com>
2006-04-05 02:49 ` Michael Fuhr <mike@fuhr.org>
2006-04-05 04:37 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox