SQL/Postgres: comparing timestamps across rows

Question

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:

2's start_time takes place on the same calendar day (2014-06-24) as 1; and
both rows have the same vehicle_id

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

score 2 · Accepted Answer · 2014-11-04 22:38:34Z

2

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

edited Nov 4, 2014 at 22:38

answered Nov 4, 2014 at 21:51

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Aupajo Over a year ago

Thanks very much! This looks the right approach to me. I'm not getting any results though: sqlfiddle.com/#!12/7c92e/3

user330315 Over a year ago

@Aupajo: my assumption that you want it sorted by id apparently was wrong. Just change the order by id to order by start_time and you'll get the ids: 20,22,4692,5344 Not sure why you expect 5297,5355 because 5297 ends on 2014-10-27 and 5355 starts on 2014-10-28 which is clearly not the same day.

Aupajo Over a year ago

Perfect, thank you! The goal is to find trips that “[take] place on the same calendar day or the next calendar day” – but you've shown me enough that I can take it from here :) Thanks.

user330315 Over a year ago

If you want to include the next day, you can change the condition to diff_to_next <= 1

Collectives™ on Stack Overflow

SQL/Postgres: comparing timestamps across rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related