1

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

1

2 Answers 2

0

You should have a look at the date_bin() function, then group by the result :

SELECT date_bin('400 millisecond', now(), TIMESTAMP '2001-01-01'), now();
         date_bin         |              now              
--------------------------+-------------------------------
 2023-06-10 13:14:22.4+00 | 2023-06-10 13:14:22.618794+00
(1 row)

Doc : https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-BIN

2
  • Thanks! But the problem of date_bin is, that grouping by bin gives indeed one timestamp entry per 400 ms-bin, but it is still possible that one entry is close to the end of the bin, and the next one is close to the beginning of the next one, making the difference between them shorter than 400ms
    – eddie_jung
    Commented Jun 10, 2023 at 18:04
  • Have you tried to change the origin parameter ?
    – slardiere
    Commented Jun 11, 2023 at 9:52
0

I found the solution, and was actually using two solutions one after the other. First, binning and filtering the data as suggested by @slardiere, and then selecting only the consecutive entries, as with the step one I have already discarded the case where more than 2 entries are closer than 400ms. The solution looks like this:

with preprocessed as    (
                -- Create series of bins sepparated by parameter ms
                with bins  as (    
                        select generate_series(
                        (select min("timestamp") from temporal.samples sd),
                        (select max("timestamp") from temporal.samples sd),
                        '800000 microseconds') as time_bin
                        )
                 select token, timestamp,extract(milliseconds from timestamp-lag(timestamp,1) over (order by timestamp)) as diff 
                 from   (
                         -- Select one timestamp per bin
                         select distinct on (time_bin) token,timestamp,time_bin
                         from temporal.samples s
                         join bins
                         on s."timestamp" between bins.time_bin and bins.time_bin + interval '800000 microseconds'
                        ) as t3
                        )
 select * from preprocessed
 -- Select the entries with a difference of parameter ms or greater
 where diff > 800 or diff isnull 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.