CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Frank Liao
Frank Liao

Reputation: 965

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

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

Upvotes: 1

Views: 302

Answers (1)

Sabari
Sabari

Reputation: 244

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

Upvotes: 1

Related Questions