I'm requesting a list of DATETIME(3) ordered by ascending order such as:
> SELECT occurred_at FROM events ORDER BY ASC;
+-------------------------+
| occurred_at |
+-------------------------+
| 2023-11-07 10:21:34.517 |
| 2023-11-07 10:20:34.524 |
| 2023-11-07 10:19:34.514 |
| 2023-11-07 10:18:34.517 |
| 2023-11-07 10:17:34.512 |
| 2023-11-07 10:16:34.517 |
| 2023-11-07 10:15:34.518 |
| 2023-11-07 10:14:34.515 |
+-------------------------+
This request is pretty simple, but I would like to measure the time between each two dates (meanings compare elapsed times between the first result and the second result in a row, then the third and the fourth in another, etc... without comparing the second with the third, the fourth with the fifth, etc...).
The native TIMEDIFF(date1, date2) function can help me to achieve this goal, but since I My goal is to group them (maybe using GROUP_CONCAT() ?) by two, I would like to have a list looking like:
> THE_QUERY;
+---------------------------------------------------+
| occurred_at |
+---------------------------------------------------+
| 2023-11-07 10:21:34.517, 2023-11-07 10:20:34.524 |
| 2023-11-07 10:19:34.514, 2023-11-07 10:18:34.517 |
| 2023-11-07 10:17:34.512, 2023-11-07 10:16:34.517 |
| 2023-11-07 10:15:34.518, 2023-11-07 10:14:34.515 |
+---------------------------------------------------+
Or even better, the
TIMEDIFF()result of date1 and date2 in a row, date3 and date4 in the another, etc...
Since I can have many rows, I'm searched for a solution using modulo 2 and co but didn't succeed to make it works.
Usually you have some information what rows belong together. But if you want to rely only on the column, you can do:
select min(occurred_at), max(occurred_at), timediff(max(occurred_at), min(occurred_at))
from (
select occurred_at, cast((row_number() over (order by occurred_at))/2 as unsigned) as rn
from events
) as q
group by rn desc
order by 1
See a dbfiddle group by rn desc order by 1