How can I get every 5 mins 1 row in Postgres?
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
1 Answer
1
Below query will produce the desired result.
I have edited my answer. Please check it and tell my if still need changes
– Sabari
Jul 3 at 6:47
In your sql and insert sample data it's fine, but how can I use my table? and what is rnk?
– Frank Liou
Jul 3 at 7:04
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
– Sabari
Jul 3 at 7:08
oh!! I see...I am so dumb...I just remove rnk....by the way It works, thank you
– Frank Liou
Jul 3 at 7:11
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
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.
– Frank Liou
Jul 3 at 6:17