CODEWITHSUNDEEP
sqlmariadb
acuz3r
acuz3r

Reputation: 43

Get time between two dates

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

What I am trying to obtain :

user id time
user3 4 07:30:00
user1 2 03:00:00
user2 1 02:00:00

The rows can be all mixed up since I dont know whenever a user will open (IN) or close (OUT) the job.

So far i tried to make a first query :

SELECT * 
    FROM table 
    WHERE DATE(date) BETWEEN '2021-02-01' AND '2021-02-02' 
    ORDER BY date DESC;

And then I was thinking looping on the result array, and make the math from there, but I was wondering if there is anyway to do this in SQL only.

Upvotes: 0

Views: 428

Answers (3)

Morten
Morten

Reputation: 148

Try this simple solution:

   SELECT T1.id, T1.time AS INTime, T1_1.time AS OUTTime
   FROM T1 
   INNER JOIN T1 AS T1_1 ON (T1.id = T1_1.id AND T1_1.Status="OUT")
   WHERE T1.Status="IN";

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can sort the result as out first then get the previous time from next row using Lead(). Though I am no expert in mariaDB but below query should work:

  with userresult as(
    SELECT *,lead(TIME,1)OVER(PARTITION BY id ORDER BY STATUS DESC) previoustime,ROW_NUMBER()over(partition by id order by status desc) rownum FROM USERS
where  DATE(date) BETWEEN '2021-02-01' AND '2021-02-02' 
    )
    select user,id,timediff(time,previoustime)time from userresult where rownum=1

If your mariaDB version is lower than 10.2 you can try simple sql like below:

select username,id,timediff(time,(select max(time) from users u where status='in'and u.id=us.id ))
from users us where status='out'

Upvotes: 1

Popeye
Popeye

Reputation: 35930

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;

Upvotes: 1

Related Questions