CODEWITHSUNDEEP
mysqlsqlgroup-by
Captain
Captain

Reputation: 13

Grouping into interval multiple columns

I have a MySql table like this:

+-------+---------------------+---------------------+------------+------------+
| id    | starttime           | endtime             | startv     | endv       |
+-------+---------------------+---------------------+------------+------------+
| 66612 | 2018-01-15 17:14:00 | 2018-01-15 17:14:59 | 0.01       | 1.002      |
| 66611 | 2018-01-15 17:13:00 | 2018-01-15 17:13:59 | 5.002      | 0.211      |
| 66610 | 2018-01-15 17:12:00 | 2018-01-15 17:12:59 | 1.001      | 2.011      |
| 66609 | 2018-01-15 17:11:00 | 2018-01-15 17:11:59 | 0.678      | 0.751      |
| 66607 | 2018-01-15 17:10:00 | 2018-01-15 17:10:59 | 0.201      | 1.752      |

I can grouping into interval of 5 minutes within a time range with:

SELECT * from activation
GROUP BY UNIX_TIMESTAMP(starttime) DIV 900
ORDER BY starttime DESC;

My current output is:

| 2018-01-15 17:10:00 | 2018-01-15 17:10:59 | 0.201      | 1.752      |

This give me a table grouped by starttime and the correct "startv" (0.201 in the table), what I need is join this with a column that contains the "endv" value that matching the last value of the group (the last "endtime" 1.002) instead of 1.752 with the correct "endtime" like:

+---------------------+---------------------+------------+------------+
| starttime           | endtime             | startv     | endv       |
+---------------------+---------------------+------------+------------+
| 2018-01-15 17:10:00 | 2018-01-15 17:14:59 | 0.201      | 1.002      |

Upvotes: 1

Views: 75

Answers (1)

Barmar
Barmar

Reputation: 780688

Write a subquery that gets the first and last timestamps in each group, then join with those to get the corresponding startv and endv.

SELECT r.starttime, r.endtime, afirst.startv, alast.endv
FROM (SELECT MIN(starttime) as starttime, MAX(endtime) AS endtime
      FROM activation
      GROUP BY UNIX_TIMESTAMP(starttime) DIV 300) AS r
JOIN activation AS afirst ON afirst.starttime = r.starttime
JOIN activation AS alast ON alast.endtime = r.endtime
ORDER BY r.starttime DESC

DEMO

And for 5-minute intervals you should be dividing by 300, not 900 (that's 15 minutes).

Upvotes: 2

Related Questions