From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | Goran Hasse <gorhas(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to fetch values at regular hours? |
Date: | 2010-05-25 21:05:37 |
Message-ID: | 4BFC3BA1.1000702@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Goran Hasse wrote:
> Hello all!
>
> I am logging my electricity meeter every five minute!
>
> But the problem is that I would like to get values *just before* every
> hour - like
>
> select * from counter_log_view where name='CNT-3' and timestamp <
> '2010-05-23 22:00:00' order by timestamp desc limit 1;
> name | timestamp | count
> -------+----------------------------+-------
> CNT-3 | 2010-05-23 21:53:20.844422 | 69
> (1 rad)
>
> But I don't want to do a lot of selections and gather all the data in
> a file.
> Is there any better way to create the query so that I get the values
> just before
> every hour?
wild guess, untested.. something like...
select * from counter_log_view where name = 'CNT-3' and extract(minute
from timestamp) >= 55 order by timestamp;
From | Date | Subject | |
---|---|---|---|
Next Message | akp geek | 2010-05-25 21:06:24 | Re: export data to excel |
Previous Message | Goran Hasse | 2010-05-25 20:54:20 | How to fetch values at regular hours? |