-1

sorry for my bad formating, I've never used this platform.

I have a table with some timestamps and I want to select the ones which have more than 30 seconds from each other and are consecutive.

It goes like this:

table called 'nova' with only one column as 'tempo'.

Tempo
2021-06-04 13:48:46-03
2021-06-04 13:48:50-03
2021-06-04 13:54:06-03
2021-06-04 13:56:30-03
2021-06-04 13:56:31-03
2021-06-04 13:58:57-03
2021-06-04 13:58:59-03
2021-06-04 14:01:30-03
2021-06-04 14:01:35-03
2021-06-04 14:04:08-03
2021-06-04 14:06:45-03

I want to select the ones that are spaced as least 30 seconds apart. But whenever I state:

"select distinct t1.* from nova t1, nova t2 where t2.tempo-t1.tempo > '00:00:30';"

Postgres does not filter any entry as it operates all tuples with one another and there is always one combination where the difference is greater than 30 seconds.

I need to filter the consecutive entries, prioritizing the older entries, as follows:

Tempo
2021-06-04 13:48:46-03
2021-06-04 13:54:06-03
2021-06-04 13:56:30-03
2021-06-04 13:58:57-03
2021-06-04 14:01:30-03
2021-06-04 14:04:08-03
2021-06-04 14:06:45-03

thanks for your help!

Lucas Lago de Lima
  • 1
  • 1
  • This is sometimes called a [tag:gaps-and-islands] problem, for your information – O. Jones Jul 06 '21 at 22:46
  • Only regarding consecutive timestamps or just any other timestamp that is 30 seconds apart? Only older ones, only younger ones or both? For clarity [edit] the question and provide a [example], i.e. the `CREATE` statements of the tables or other objects involved (paste the **text**, don't use images, don't link to external sites), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. And show what you have tried already **explicitly** -- post the code. Explain why/where it failed. Be specific (error message, unexpected result, etc.). – sticky bit Jul 06 '21 at 22:49
  • I will edit and clarify. I am trying to find the consecutive timestamps spaced not less than 30 seconds. – Lucas Lago de Lima Jul 06 '21 at 23:02

1 Answers1

0

This query solves that problem:

with q as (select tempo, lead(tempo) over (order by tempo) from nova)
select tempo from q where lead is null or age(lead, tempo) > '30 seconds';
Jaime Casanova
  • 56
  • 3