CODEWITHSUNDEEP
sqlpostgresqldatetimetimestamp
SackZement
SackZement

Reputation: 43

Get difference of two timestamp columns in two different rows

I have the following PostgreSQL table "event"

CREATE TABLE event (
    user INTEGER,
    name VARCHAR(255),
    begin_timestamp TIMESTAMP,
    end_timestamp TIMESTAMP
);

Data in the table:

╔══════╦══════╦═════════════════════╦═════════════════════╗
║ user ║ name ║   begin_timestamp   ║    end_timestamp    ║
╠══════╬══════╬═════════════════════╬═════════════════════╣
║ 5    ║ foo  ║ 2020-07-23 16:11:00 ║ 2020-07-23 16:28:00 ║
║ 5    ║ bar  ║ 2020-07-23 16:40:00 ║ 2020-07-23 16:40:00 ║
║ 5    ║ abcd ║ 2020-07-23 00:00:00 ║ 2020-07-23 00:37:00 ║
║ 7    ║ foo  ║ 2020-07-23 18:12:00 ║ 2020-07-23 18:23:00 ║
║ 7    ║ bar  ║ 2020-07-23 11:00:00 ║ 2020-07-23 11:00:00 ║
║ 8    ║ foo  ║ 2020-07-23 00:00:00 ║ 2020-07-23 03:00:00 ║
║ ...  ║ ...  ║ ...                 ║ ...                 ║
╚══════╩══════╩═════════════════════╩═════════════════════╝

How can I get the time between two events for a specific user? (NOT the duration of a specific event)

Example: The time between the event "foo" and the event "bar" for the user "5" was 12 minutes, because the event "foo" ended at 2020-07-23 16:28:00 and the event "bar" began at 2020-07-23 16:40:00.

So far I have these queries:

SELECT end_timestamp FROM event WHERE name = 'foo' and user = 5
SELECT begin_timestamp FROM event WHERE name = 'bar' and user = 5
SELECT (end_timestamp - begin_timestamp) AS duration FROM event WHERE name = 'foo' AND user = 5;

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use lag(). Assuming at most one "foo" and "bar" per event:

select e.*,
       (begin_timestamp - prev_end_timestamp) as duration
from (select e.*, lag(end_timestamp) over (partition by name order by begin_timestamp) as prev_end_timestamp
      from event e
      where e.name in ('foo', 'bar')
     ) e
where e.name = 'bar';

You can also phrase this using aggregation:

select user,
       ( max(begin_timestamp) filter (where name = 'bar') -
         min(end_timestamp) filter (where name = 'foo')
       ) as duration
from event e
where e.name in ('foo', 'bar')
group by user

Upvotes: 1

Related Questions