Re: How to fetch values at regular hours?

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