I need a report of which cars (license plates) are parked in and when.

This is an example of my table.

id  lic_plate   datetime                   lane    
_________________________________________________
10  1234-JM     2022-10-07 12:24:33        IN    
11  1234-JM     2022-10-07 12:29:57        OUT
12  5678-PC     2022-10-07 15:14:17        IN

So when I query which are those who are parked more than - for instance - 1hour, the result Now (2022-10-07 15:14:17) should be "5678-PC".

I have tried:

SELECT lic_plate, COUNT(*) AS result
FROM table
GROUP BY lic_plate
HAVING COUNT(lane='IN') AND COUNT(lane='OUT')

But I can't figure out where I insert the HOUR query. And it seems to me that this kind of solution will have some "hidden" flaws.

What will be the best approach? Thanks in advance.

CodePudding user response：

select   max(id) as id  
        ,lic_plate  
        ,max(datetime)                 as datetime
        ,timediff(now(),max(datetime)) as time_parked
from     t
group by lic_plate
having   count(*)%2 != 0

Fiddle

CodePudding user response：

We check the time difference between the current date and time and datetime. We count how many records we have per car and if we have even numbers of records we filter the results as we know the car is out already.

select  id  
       ,lic_plate   
       ,datetime
       ,time_parked
from    (
        select  *
                ,count(*) over(partition by lic_plate) as chk
                ,timediff(now(),datetime) as time_parked
        from    t
        ) t
where   chk%2 != 0

Fiddle