Hi Robert
Interesting problem. I need to think about it.
You need to figure out when Input changes. You can achieve this by using lead or lag (depending of the sort direction over start) https://www.postgresql.org/docs/current/functions-window.html .
Hope this nudges you to a solution.
Kind regards
Thiemo
Am 04.06.22 um 10:18 schrieb Robert Stanford:
Hi,
I have time series data from multiple inputs with start and
end timestamps.
Can anyone suggest an elegant way to coalesce consecutive rows so only the
first start time and last end time for each group of events (by input) is returned.
Sample from and to below where the rows for Input number 4 could be massaged.
(Timestamps selected with timestamp(0) for convenience)
From this:
Input Start End
5 2022-06-04 09:09:00 2022-06-04 09:09:29
4 2022-06-04 09:08:50 2022-06-04 09:09:00
4 2022-06-04 09:08:10 2022-06-04 09:08:50
4 2022-06-04 09:07:47 2022-06-04 09:08:10
17 2022-06-04 09:06:47 2022-06-04 09:07:47
4 2022-06-04 09:06:37 2022-06-04 09:06:47
4 2022-06-04 09:06:29 2022-06-04 09:06:37
4 2022-06-04 09:06:17 2022-06-04 09:06:29
4 2022-06-04 09:05:53 2022-06-04 09:06:17
16 2022-06-04 09:04:33 2022-06-04 09:05:53
To this:
Input Start End
5 2022-06-04 09:09:00 2022-06-04 09:09:29
4 2022-06-04 09:07:47 2022-06-04 09:09:00
17 2022-06-04 09:06:47 2022-06-04 09:07:47
4 2022-06-04 09:05:53 2022-06-04 09:06:47
16 2022-06-04 09:04:33 2022-06-04 09:05:53
Thanks in advance to anyone who can help!
Robert
