I have separate data like below in PostgreSQL. I want to group by data based in rounding date and time with X minute. Cost column will has aggregate function like min and max.
| Date | Time | Cost |
|---|---|---|
| 2021-05-14 | 10:18:00 | 10 |
| 2021-05-14 | 10:20:00 | 20 |
| 2021-05-14 | 10:21:00 | 30 |
| 2021-05-14 | 10:22:00 | 40 |
| 2021-05-14 | 10:31:00 | 50 |
| 2021-05-14 | 10:48:00 | 60 |
| 2021-05-14 | 10:59:00 | 70 |
| 2021-05-14 | 11:00:00 | 80 |
| 2021-05-14 | 11:14:00 | 90 |
| 2021-05-14 | 11:15:00 | 100 |
| 2021-05-14 | 11:17:00 | 110 |
I want it round out to nearest 3, 5,10,15,30,60,120,240 minutes. But what specific i want to do is, when it is rounding out with round figure digits like 10,30,60,120,240 it should start with 10:15:00. ( means it should with minute 15.)
For example,
for 5 minutes, above data from 10:15:00 to 10:19:00 should be round out to 10:15:00 (Total 5 minutes considered, 10:15, 10:16, 10:17, 10:18, 10:19)
for 10 minutes, above data from 10:15:00 to 10:24:00 should be round out to 10:15:00
for 15 minutes, above data from 10:15:00 to 10:29:00 should be round out to 10:15:00
for 30 minutes, above data from 10:15:00 to 10:44:00 should be round out to 10:15:00
for 60 minutes, above data from 10:15:00 to 11:14:00 should be round out to 10:15:00
for 120 minutes, above data from 10:15:00 to 12:14:00 should be round out to 10:15:00
for 240 minutes, above data from 10:15:00 to 14:14:00 should be round out to 10:15:00
I have tried to go through many stackoverflow answer but my specific case i couldn't found.
Whichever answers i have tried of similar question either they round out to 10:10:00 for 10 minutes or for higher minutes, it starts with 10:00:00.
We have huge data set so performance is also a point to consider here.
Can anyone help for this specific case?
