Search code examples
sqlpostgresqlintervalspostgresql-9.5sql-timestamp

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!

Solution

  • 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