CODEWITHSUNDEEP
sqlpostgresqltimestamptimespan
Aupajo
Aupajo

Reputation: 6055

SQL/Postgres: comparing timestamps across rows

I have a table called trips that looks like this:

 id | vehicle_id | start_time          | end_time            |
----+------------+---------------------+---------------------+
  1 |          1 | 2014-06-16 22:00:00 | 2014-06-24 03:30:00 |
  2 |          1 | 2014-06-24 05:00:00 | 2014-06-28 05:00:00 |
  3 |          2 | 2014-06-23 02:00:00 | 2014-06-30 04:00:00 |
 ...

SQL Fiddle: http://www.sqlfiddle.com/#!12/7c92e/1 (PG 9.2 only because 9.3 was overloaded on SQL Fiddle the time.)

start_time and end_time are both timestamps.

What I'd like to do find trips involving the same vehicle_id where the start_time of a subsequent trip takes place on the same calendar day or the next calendar day of the end_time of a prior trip.

As an example, rows with ID 1 and 2 above would be returned, because:

It's quite possible this isn't sensible to do in SQL. Any advice, tips or pointers to relevant Postgres functions welcome.

Upvotes: 0

Views: 243

Answers (1)

user330315
user330315

Reputation:

Access to values from the "previous" row can be done using window functions. In this case the lag() function. 

select id, vehicle_id, start_time, end_time
from (
  select id, vehicle_id, start_time, end_time,
         start_time::date - lag(end_time::date) over (partition by vehicle_id order by start_time) as diff_to_prev,
         end_time::date - lead(start_time::date) over (partition by vehicle_id order by start_time) as diff_to_next
  from trips
) t
where diff_to_prev = 0
   or diff_to_next = 0;

"previous" only make sense if you also supply a sort order. From your description it sounds as if you want to use the id column to determine the order of the rows. But if you can easily change that to use start_date.

The statement calculates the difference in days to the next row and the previous row. If only the difference to the previous was taken, the row with id=1 would not be returned.

The expression start_time::date simply converts the timestamp to a date to remove the time part. This also has the effect that the difference is an integer value (in days) rather than an interval.

SQLFiddle: http://www.sqlfiddle.com/#!12/7c92e/5

Upvotes: 2

Related Questions