0

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps.

Data is timestamp without timezone formatted and an example is the following 10 dates:

CREATE TABLE test
(
    measurement_timestamp timestamp without time zone
)
WITH (
    OIDS = FALSE
);

INSERT INTO test (measurement_timestamp ) VALUES

'2019-11-14 08:22:00',
'2019-11-14 11:10:00',
'2019-11-14 11:11:00',
'2019-11-14 11:12:00',
'2019-11-14 11:13:00',
'2019-11-14 11:15:00',
'2019-11-14 11:16:00',
'2019-11-14 11:17:00',
'2019-11-14 11:18:00',
'2019-11-14 11:19:00';

I need to find the following elements: gap > 1 minute

GAPID (can be any sequential number or ignored if needed) StartDateTime,EndDateTime,Gapsize in minutes

Example:

(1,'2019-11-14 08:22:00','2019-11-14 11:10:00', 168)

previous questions asked commonly have a grouping based on a member or other but i simply need the gap in a sequence to find my datagaps. Data attached to the date cannot be used for grouping and cannot be shared nor do i really care. i just need to find the gaps > 1 minute.

EDIT:

Im now looking for ISLAND size:

so assuming i have same list as before i now would like the following output:

'GAP','2019-11-14 08:22:00','2019-11-14 11:10:00', 168
'ISLAND','2019-11-14 11:10:00','2019-11-14 11:13:00', 3
'GAP','2019-11-14 11:13:00','2019-11-14 11:15:00',2
'ISLAND','2019-11-14 11:15:00','2019-11-14 11:19:00',5

can that be done via sql as well?

1

You need to first calculate the gap and then use that in a second level to calculate the longest gap (this can't be done in a single statement, as you can't nest window function calls):

select *
from (
  select rn, 
         prev_measurement,
         measurement_timestamp,
         gap,
         dense_rank() over (order by gap desc) as rnk
  from (
    select row_number() over w as rn,
           measurement_timestamp,
           lag(measurement_timestamp) over w as prev_measurement,
           measurement_timestamp - lag(measurement_timestamp) over w as gap
    from test
    window w as (order by measurement_timestamp)
  ) t1
  where gap > interval '1 minute'
) t2
where rnk = 1;

Online example

| improve this answer | |
  • awewsome i can get that gap working indeed. can i also get the inverse done? show how large the continuous chain is before the gap? (island size)? aka: ``` island,starttime,endtime,size_in_minutes gap,starttime,endtime,size_in_minutes ``` – Alex Vermeulen Mar 18 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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