Grouping data into 5 minutes interval within a time range

by Uttam   Last Updated January 14, 2018 11:06 AM

I am trying to prepare data for candle stick chart. i have some records on mySQL database as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.001111 | 2017-11-12 19:13:05 |
| 0.001214 | 2017-11-12 19:14:11 |
| 0.001612 | 2017-11-12 19:15:42 |
| 0.001231 | 2017-11-12 19:16:23 |
| 0.001514 | 2017-11-12 19:17:23 |
| 0.001611 | 2017-11-12 19:26:33 |
| 0.001421 | 2017-11-12 19:27:25 |
| 0.001214 | 2017-11-12 19:28:27 |
| 0.001611 | 2017-11-12 19:29:55 |
|----------|---------------------|

I want to group below data as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.002325 | 2017-11-12 19:10:00 |
| 0.004357 | 2017-11-12 19:15:00 |
| 0.004357 | 2017-11-12 19:20:00 |  # Auto generated record of missing record.
| 0.005857 | 2017-11-12 19:25:00 |
|----------|---------------------|

i was trying to understand calendar table, but i wasn't able to do with that. i also followed this link https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range but still failed.

Tags : mysql


Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM



MySQL Upgrade on Windows

Updated June 17, 2015 23:02 PM

MYSQL:Community Server Patching

Updated October 19, 2017 16:06 PM