Question 113292 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to check how many time a car is parked on

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.

Tags:

2

Answers


  1. 0

    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
    id lic_plate datetime time_parked
    12 5678-PC 2022-10-07 15:14:17 00:29:58

    Fiddle

    Login or Signup to reply.
  2. 0 
    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
    id lic_plate datetime time_parked
    12 5678-PC 2022-10-07 15:14:17 69:26:12

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search