CODEWITHSUNDEEP
postgresqldatetime
user3925023
user3925023

Reputation: 687

Postgres Query timediff on sequentials days

I have this type of records: enter image description here

I need to compute the time difference beetween arrival and departure, also considering the case departure will be the next day, like Car 5 above example. What could be an approach to do so? Many thanks

###UPDATE Unintentionally I did put a wrong data (changing months instead days), pls see updated ones

Upvotes: 1

Views: 82

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

Assuming there is a always an arrival time for every departure of car. You can try below query:

select 
*, 
case
when arrival is not null and departure is not null then departure-arrival
when arrival is null and departure is not null  and car=lag(car) over (order by car,arrival nulls last) then 
departure - lag(arrival) over (order by car,arrival nulls last)
end
from test

DEMO

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12494

Use a window function:

with span_days as (
  select car, 
         arrival,
         coalesce(
           departure,
           lead(departure) over (partition by car 
                                     order by coalesce(arrival, departure))
         ) as departure 
    from car_arrive_depart
)
select car, arrival, departure, 
       departure - arrival as time_diff
  from span_days
 where arrival is not null;

This does not address how to deal with cars that are still departed at the time of the query, and it does extend beyond only the next day.

Upvotes: 1

Related Questions