SQL: How to determine how long object in same location (Snowflake)

Question

I am trying to determine how long an object is in the same location, and what time it was first in that location for that duration. If it leaves the location and comes back, I need another row with a new time calculation.

Data:

ObjectID	DateTime	Lat	Lon
23	5/2/2021 12:00	38.09	20.99
23	5/2/2021 16:00	40.11	30.34
23	5/2/2021 23:00	40.11	30.34
23	5/3/2021 12:00	40.11	30.34
23	5/3/2021 16:00	40.11	30.34
23	5/3/2021 23:00	39.88	29.00
23	5/4/2021 12:00	39.88	29.00
23	5/4/2021 16:00	20.77	11.66
23	5/4/2021 23:00	40.11	30.34
23	5/5/2021 12:00	40.11	30.34
23	5/5/2021 16:00	20.77	11.66

Desired Results:

ObjectID	StartTime	Lat	Lon	HoursInLocation
23	5/2/2021 16:00	40.11	30.34	24:00
23	5/3/2021 23:00	39.88	29.00	13:00
23	5/4/2021 23:00	40.11	30.34	13:00

My real data does not have a consistent DateTime frequency, I used it here to make it easier to read. Real data also has multiple ObjectID's. Location of the object between entries is considered unknown.

Thanks!

Answer 1

This is a type of gaps-and-island problem. For this version, the difference of row numbers is probably the best solution:

select objectid, lat, lng, min(datetime), max(datetime),
       datediff(minute, min(datetime), max(datetime)) / 60.0 as hours_diff
from (select t.*,
             row_number() over (partition by objectid, lat, lng order by datetime) as seqnum_2,
             row_number() over (partition by objectid order by datetime) as seqnum
      from t
     ) t
group by objectid, lat, lng, (seqnum - seqnum_2)
having count(*) > 1;

This returns each period of time when an object is at a location.