Get average time difference between current and previous row
Solution 1:
Assuming you're using MySQL 8 or later you can use lag function to find the previous datetime in order to calculate the difference. Rest is straight forward:
with cte as (
select cast(ts as date) as dt
, ts
, lag(ts) over(partition by cast(ts as date) order by ts) as prev_ts
from t
)
select dt
, count(*) as count
, avg(timestampdiff(second, prev_ts, ts)) as avg_diff
from cte
group by dt