Geek asks and answers

Postgres SQL selects a range of records separated by a given interval

I am trying to determine if it is possible, using only sql for postgres, to select a range of time-ordered entries for a certain interval.

Suppose I have 60 entries, one entry for every minute at a given hour. I want to select recordings at 5 minute intervals during this hour. The resulting rows should be 12 entries, each of which is at a distance of 5 minutes.

Currently, this is achieved by selecting the full range of records, and then cycling through the results and pulling out the records at a given interval. I am trying to figure out if I can do this purely in sql since our db is big and we can deal with tens of thousands of records.

Any thoughts?

+3
source share
5 answers

Yes, you can. Its really easy as soon as you get it. I think this is one of the treasures of SQL and it is especially lightweight in PostgreSQL because of its excellent temporary support. Often complex functions can turn into very simple SQL queries that can scale and index properly.

This uses generate_series to create selective timestamps located 1 minute apart. The external query then retrieves the minute and uses modulo to find values ​​that are 5 minutes apart.

select
    ts,
    extract(minute from ts)::integer as minute

    from
    ( -- generate some time stamps - one minute apart
        select
            current_time + (n || ' minute')::interval  as ts
        from generate_series(1, 30) as n
    ) as timestamps
    -- extract the minute check if its on a 5 minute interval
    where extract(minute from ts)::integer % 5 = 0
    -- only pick this hour 
    and extract(hour from ts) = extract(hour from current_time)
;
         ts         | minute 
--------------------+--------
 19:40:53.508836-07 |     40
 19:45:53.508836-07 |     45
 19:50:53.508836-07 |     50
 19:55:53.508836-07 |     55

, where ( ) . , , .

PostgreSQL ( , ) .

http://www.amazon.com/SQL-Design-Patterns-Programming-Focus/dp/0977671542 - , . , .

+6

, int4 , 5 0:

select * 
  from TABLE 
  where int4 (date_part ('minute', COLUMN)) % 5 = 0;
+1
  • , ;
  • ,

5

select *
from
(
  select *, row_number() over (order by timecolumn) as rown
  from tbl
) X
where mod(rown, 5) = 1

, ( ) , ( ) MAX , .

select thetimeinterval, max(timecolumn)
from ( < the time series subquery > ) X
left join tbl on tbl.timecolumn <= thetimeinterval
group by thetimeinterval

( )

select t.* from
tbl inner join
(
    select thetimeinterval, max(timecolumn) timecolumn
    from ( < the time series subquery > ) X
    left join tbl on tbl.timecolumn <= thetimeinterval
    group by thetimeinterval
) y on tbl.timecolumn = y.timecolumn
+1

:

select min(ts), extract(minute from ts)::integer / 5 
   as bucket group by bucket order by bucket;

, , , . min first(), :

http://wiki.postgresql.org/wiki/First_%28aggregate%29

0

This assumes that your five-minute intervals are, so to speak, "five." That is, you want 07:00, 07:05, 07:10, not 07:02, 07:07, 07:12. It also assumes that you do not have two lines in one minute, which may be unsafe.

select your_timestamp
from your_table
where cast(extract(minute from your_timestamp) as integer) in (0,5);

If you can have two lines with one minute timestamps, for example

2011-01-01 07:00:02
2011-01-01 07:00:59

then this version is more secure.

select min(your_timestamp)
from your_table
group by (cast(extract(minute from your_timestamp) as integer) / 5)

Wrap any of them in a view, and you can join it in your base table.

0
source

More articles:


All Articles