Thread: How to fetch values at regular hours?

How to fetch values at regular hours?

From

Goran Hasse

Date:

25 May 2010, 20:54:30

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?

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?

--
gorhas@gmail.com
Mob: 070-5530148

Re: How to fetch values at regular hours?

From

John R Pierce

Date:

25 May 2010, 21:06:02

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;

Re: How to fetch values at regular hours?

From

Tim Landscheidt

Date:

25 May 2010, 21:07:19

Goran Hasse <gorhas@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 :-).

Re: How to fetch values at regular hours?

From

Goran Hasse

Date:

25 May 2010, 21:21:41

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@tim-landscheidt.de>

Goran Hasse <gorhas@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

--
gorhas@gmail.com
Mob: 070-5530148

Re: How to fetch values at regular hours?

From

Tim Landscheidt

Date:

25 May 2010, 22:59:48

Goran Hasse <gorhas@gmail.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