From: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to fetch values at regular hours? |
Date: | 2010-05-25 21:06:57 |
Message-ID: | m339xfzo5q.fsf@passepartout.tim-landscheidt.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Goran Hasse <gorhas(at)gmail(dot)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 :-).
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2010-05-25 21:10:04 | Re: export data to excel |
Previous Message | akp geek | 2010-05-25 21:06:24 | Re: export data to excel |