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 22:59:20 |
Message-ID: | m3sk5fy4dz.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:
> 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...
Why did you not use the query I posted:
> [...]
>> Sure:
>> | SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
>> | FROM counter_log_view
>> | ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
> [...]
Is copy & paste too much effort?
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | Azlin Rahim | 2010-05-25 23:09:00 | Re: Mysterious empty database name? |
Previous Message | Marc Munro | 2010-05-25 22:49:31 | Re: Hiding data in postgresql |