How can I get every 5 mins 1 row in Postgres?

Question

I have a Table, there are three column.

id, time and value

how can I get ervery Id is every 5 mins range only one data in Postgres?

ex:

id    time    value 

1     03:11    test
1     07:13    test
1     12:11    test
2     02:11    test
2     07:11    test

This is my version, but only get by one hour...

select
  distinct on("id")
  "id",
  time::time,
  value
from (
select
 "id",
 time::time,
 value,
dense_rank() over (
  partition by "id"
  order by
  to_char(time,'HH')::int,
  (
    to_char(time,'MI')
  )::int/5
)
from my_table
where time between '2018-07-03 00:00:00' and '2018-07-03 01:00:00' and "id" = 'XXXXX'
) as res
order by "id";

result:

id   time  value
1     03:05   14

but the result pose to be 10 rows.. maybe more

Sabari · Accepted Answer · 2018-07-03 07:40:44Z

1

Below query will produce the desired result.

with res(id,time,value,rnk) as (
select
 id,
 time,
 value,
dense_rank() over (
  partition by id
  order by 
  to_char(time,'HH24'),
  (
    to_char(time,'MI')
  )::int/5
)
from your_table
)
select
  distinct on(id,rnk)
  id,
  to_char(time,'HH24:MI'),
  value
from res
order by id,rnk

edited Jul 3, 2018 at 7:40

answered Jul 3, 2018 at 5:09

Sabari

2442 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Frank Liao Over a year ago

Thanks, It works, but my time is like '2018-07-03 00:12:00' format, so when I use your way, I only slice by hours.

Sabari Over a year ago

I have edited my answer. Please check it and tell my if still need changes

Frank Liao Over a year ago

In your sql and insert sample data it's fine, but how can I use my table? and what is rnk?

Sabari Over a year ago

rnk is the column name for dense_rank() calculation. I have removed my sample data. Now replace "your_table" in query to corresponding table name

Collectives™ on Stack Overflow

How can I get every 5 mins 1 row in Postgres?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related