Sample Header Ad - 728x90

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. enter image description here 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:
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: enter image description here 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
Asked by eddie_jung (11 rep)
Jun 10, 2023, 10:56 AM
Last activity: Jul 20, 2025, 08:04 PM