Yes timestamp and count - is not good names for columns... I tried something like;
select name,date_trunc('hour',timestamp),timestamp,count from counter_log_view where name='CNT-3' and timestamp < '2010-05-23 20:00:00' order by timestamp limit 10; name | date_trunc | timestamp | count -------+---------------------+----------------------------+------- CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:43:17.411386 | 23 CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:53:17.45934 | 24 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:03:17.489321 | 24 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:13:17.586089 | 24 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:23:17.69116 | 25 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:33:17.795955 | 28 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:43:17.89265 | 28 CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:53:17.989268 | 30 CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:03:18.1447 | 33 CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:13:18.199568 | 35 (10 rader) Seems promising... But then I would like to select only the last from date_trunc... Hm... GH 2010/5/25 Tim Landscheidt <t...@tim-landscheidt.de> > Goran Hasse <gor...@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 > -- gor...@gmail.com Mob: 070-5530148