1

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      |
Captain
  • 13
  • 2
  • So If I understand correctly, you want `startv` from the earliest row in the group, and `endv` from the latest row in the group? – Jerry Jan 17 '18 at 22:56
  • @Jerry - It seems that everything starting with *start* should be from first and *end* - from last. – PM 77-1 Jan 17 '18 at 23:00
  • 66607 startv and 66612 endv, apologize for the confusion :) – Captain Jan 17 '18 at 23:03
  • You actually wrote your question very well. I just fumbled the reading. :) – Jerry Jan 17 '18 at 23:05
  • https://stackoverflow.com/questions/537223/mysql-control-which-row-is-returned-by-a-group-by might give you the answer you need, and it not it, in turn, links to more info. – Jerry Jan 17 '18 at 23:34
  • Thank you, I'm going to take a look – Captain Jan 17 '18 at 23:39

1 Answers1

2

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).

Barmar
  • 741,623
  • 53
  • 500
  • 612