1

I have a table in PostgreSQL 9.5 with two timestamps in each row, timestampstart and timestampstop.

CREATE TABLE routes(
    ID serial PRIMARY KEY,
    TIMESTAMPSTART timestamp default NULL,
    TIMESTAMPEND timestamp default NULL
);

Now I don´t want to calculate the interval between start and stop, but between stop and the next start. So I want the interval between TIMESTAMPSTOP from row x and TIMESTAMPSTART on row x+1. By the way, the ID is not in chronological order!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Martin
  • 39
  • 6

2 Answers2

1

You can't just use the simple window function lead() or lag() as you want the interval between two different columns.

Various join variants with added predicates are possible. With an index on timestampstart a LATERAL join with LIMIT 1 is probably fastest.

Assuming timestampstart to be UNIQUE, else you need to define how to break ties. A UNIQUE constraint would also provide the index needed for performance:

SELECT r.*, r1.timestampstart - r.timestampend AS interval_til_start_of_next_row
FROM   routes r
LEFT   JOIN LATERAL (
   SELECT timestampstart
   FROM   routes
   WHERE  timestampstart > r.timestampend
   ORDER  BY timestampstart  -- or BY timestampstart, id - to break ties if not unique
   LIMIT  1
   ) r1 ON true;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

If there are no overlaps between the "events", then you can do a simple window function. The query can be as simple as:

SELECT id, lead(timestampstart) OVER (ORDER BY timestampstart) -
           timestampend AS timetonext
FROM routes;

SQLFiddle

This solution is an order of magnitude faster than Erwin's solution: http://www.sqlfiddle.com/#!15/551e2/4

Community
  • 1
  • 1
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • True, with an [exclusion constraint](http://stackoverflow.com/questions/26539259/non-overlap-continuous-timestamp-ranges-tstzrange-for-opening-hours/26539981#26539981) in place, this query would be equivalent and much simpler and faster. One corner case difference: My query assumes `timestampstart > r.timestampend` while this would also allow equality: `timestampstart >= r.timestampend`. Range types (or the SQL `OVERLAPS` operator) treat the upper bound as exclusive by default, the next interval can start with the same timestamp as the last one ends and it's not considered an overlap. – Erwin Brandstetter Apr 17 '16 at 13:26