CODEWITHSUNDEEP
sqlpostgresql
user1298426
user1298426

Reputation: 3717

How to get the time difference between two rows?

I have a table like this

Task    Event               Time
2       opened          "2018-12-14 16:23:49.058707+01"
2       closed          "2018-12-14 16:24:49.058707+01"
3       opened          "2018-12-14 16:25:49.058707+01"
3       Interrupted     "2018-12-14 16:26:49.058707+01"
3       closed          "2018-12-14 16:27:49.058707+01"

I need to get the data from the table something like this

Task    Difference
2           1

The data should be fetched only when there are only 2 events opened and closed. If there are only 2 events then time difference between abs(closed - opened) should be taken.

I can not figure out figure out how to do it based on Event column

Upvotes: 1

Views: 97

Answers (4)

Zack
Zack

Reputation: 2341

Yet another option is to break your table into 3 separate derived tables: One for the opened event, one for the closed event, and one for "other" events (e.g. interrupted). Then, you can join those derived tables together to get what you need. For example (using CTEs, although you can of course inline the queries):

WITH
    -- sample data
    tbl(Task, "Event", Time) AS
    (
        VALUES
        (2, 'opened',       '2018-12-14 16:23:49.058707+01'::TIMESTAMP),
        (2, 'closed',       '2018-12-14 16:24:49.058707+01'::TIMESTAMP),
        (3, 'opened',       '2018-12-14 16:25:49.058707+01'::TIMESTAMP),
        (3, 'interrupted',  '2018-12-14 16:26:49.058707+01'::TIMESTAMP),
        (3, 'closed',       '2018-12-14 16:27:49.058707+01'::TIMESTAMP)
    ),
    -- derived tables
    opened  AS (SELECT * FROM tbl WHERE "Event" = 'opened'),
    closed  AS (SELECT * FROM tbl WHERE "Event" = 'closed'),
    other   AS (SELECT * FROM tbl WHERE "Event" NOT IN ('opened', 'closed'))
SELECT
    -- uses @S-Man's EXTRACT function to get minutes from a TIMESTAMP value.
    ABS(EXTRACT(epoch FROM (opened.Time - closed.Time)) / 60)
FROM opened
    INNER JOIN closed ON
        closed.Task = opened.task
    -- use LEFT JOIN and NULL to exclude records that have an "other" status.
    LEFT JOIN other ON
        other.Task = opened.Task
WHERE other.Task IS NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Vamsi's solution works, but it is too complicated for my tastes. I would just go for:

select task, 
       max(time) FILTER(where event = 'closed') - max(time) FILTER (where event = 'opened') 
from tbl
group by task
having count(*) = 2 and
       min(event) = 'closed' and
       max(event) = 'opened';

Or, if we don't want to depend on the string ordering:

having count(*) = 2 and
       count(*) filter (where event = 'closed') = 1 and
       count(*) filter (where event = 'opened') = 1 ;

Upvotes: 1

S-Man
S-Man

Reputation: 23666

Simplified the very good answer of @VamsiPrabhala

demo:db<>fiddle

SELECT 
    task,
    MAX(time) - MIN(time) as difference
FROM times
GROUP BY task
HAVING array_agg(event ORDER BY time) = '{"opened","closed"}'
  1. Grouping by task. But only these tasks that have exactly one opened and one closed state (in that order). This is checked by aggregating the events
  2. Because we know that there are only these two events, ordered by time, the first one (MIN) is the opened event, the last one (MAX) is the closed event.

Furthermore:

The difference between two timestamps always gets an interval type instead of your expected integer of minutes. To get the minutes, you'll need:

EXTRACT(EPOCH FROM
    MAX(time) - MIN(time)
) / 60 as difference

EXTRACT(EPOCH FROM) converts the interval into seconds. To get minutes, divide it by 60.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done using conditional aggregation.

select task
      ,max(case when event = 'closed' then time end) - max(case when event = 'opened' then time end) as diff 
--The aggregation can also be expressed using FILTER as shown below
--,max(time) FILTER(where event = 'closed') - max(time) FILTER (where event = 'opened') 
from tbl
group by task
having count(distinct case when event in ('opened','closed') then event end) = 2
and count(distinct event) = 2

Upvotes: 3

Related Questions