From: | Goran Hasse <gorhas(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to fetch values at regular hours? |
Date: | 2010-05-25 21:21:30 |
Message-ID: | AANLkTikSvqnsJkX6OzUiIeiKSh6WTT7Nofi4oEdEY1uV@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <tim(at)tim-landscheidt(dot)de>
> 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 :-).
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
gorhas(at)gmail(dot)com
Mob: 070-5530148
From | Date | Subject | |
---|---|---|---|
Next Message | Azlin Rahim | 2010-05-25 22:23:13 | Mysterious empty database name? |
Previous Message | John R Pierce | 2010-05-25 21:10:04 | Re: export data to excel |