CODEWITHSUNDEEP
sqlpostgresqlintervalspostgresql-9.5sql-timestamp
Martin
Martin

Reputation: 39

Get time interval between stop (row x) and start (row x+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!

Upvotes: 1

Views: 275

Answers (2)

Patrick
Patrick

Reputation: 32364

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

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659047

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;

Upvotes: 1

Related Questions