sql - Get time between two dates

Question

I am trying to get the time between two dates in two different rows.

dataset:

user	id	time	status
user3	4	2021-02-01 14:00:00	OUT
user1	2	2021-02-01 12:00:00	OUT
user2	1	2021-02-01 10:00:00	OUT
user1	2	2021-02-01 09:00:00	IN
user2	1	2021-02-01 08:00:00	IN
user3	4	2021-02-01 08:30:00	IN

question from:https://stackoverflow.com/questions/66050034/get-time-between-two-dates

Answer 1

You can use comditional aggregation as follows:

SELECT user, id,
       Timediff( Max(case when status='OUT' then time end),
                 Min(case when status='IN' then time end) ) as diff
    FROM table 
    WHERE DATE(date) BETWEEN '2021-02-01' AND '2021-02-02' 
    Group by user, id;