How to leave only the rows with an timestamp difference with an interval greater than a parameter
1
vote
2
answers
143
views
I have a Postgres table with timestamps as entries.
From this table, I would like to calculate a new one where there are no consecutive entries with a timestamp difference shorter than 400 milliseconds. So in the case of the image, from the first 10 rows I would only leave [1,5,9]
I tried with joins, but I realised I would need the updated table before calculating the ON clause of posterior rows, because I would need to know which rows have already been deleted.
Edit:
I tried the following join to at least have an idea of the tokens I would like to delete:
Here, I see that the next token after the first one which follows the condition, is the 5th. So I would like to delete 2,3,4. Then, the next which is 400 ms delayed from the 5th is the 9th, so I would like to delete 6,7,8.
Thanks in advance

select distinct on (s.token)s.token as token1, s.timestamp as tm1, s2.token as token2, s2.timestamp as tm2
from temporal.samples s
join temporal.samples s2
on s2.timestamp>s.timestamp + interval '400000 microseconds')
Giving this result:

Asked by eddie_jung
(11 rep)
Jun 10, 2023, 10:56 AM
Last activity: Jul 20, 2025, 08:04 PM
Last activity: Jul 20, 2025, 08:04 PM