Re: How to fetch values at regular hours? - Mailing list pgsql-general

Goran Hasse <gorhas@gmail.com> wrote:

> [...]

> I would like to do;

> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 18:00:00' order by timestamp desc limit 1;
>  name  |         timestamp         | count
> -------+---------------------------+-------
>  CNT-3 | 2010-05-23 17:53:18.58674 |    43
> (1 rad)

> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 19:00:00' order by timestamp desc limit 1;
>  name  |         timestamp          | count
> -------+----------------------------+-------
>  CNT-3 | 2010-05-23 18:53:19.151988 |    50
> (1 rad)

> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 20:00:00' order by timestamp desc limit 1;
>  name  |         timestamp          | count
> -------+----------------------------+-------
>  CNT-3 | 2010-05-23 19:53:19.683514 |    51
> (1 rad)

> In one query. Is this possible in *any* way?

Sure:

| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
|   FROM counter_log_view
|   ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;

Tim

P. S.: Naming columns "timestamp" and "count" will lead to
      trouble :-).

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general