Sina Sina - 3 months ago 8
MySQL Question

Time difference using MySQL - end time after mid night not working

I am trying to figure out whether my current time falls between two given times. It is working fine, unless if my end time falls after the mid night, while my start time is during the day.

For example, if I have:

Start: 17:00

End: 03:00

Current: 18:30

It then will not show up.

I do understand where the logic is going wrong, but I can't find a fix for it, logically and conceptually. I mean, you could claim that 06:30 PM does fall between 05 PM and 03 AM, but then 03 AM of the next day!

How do we overcome that problem?

Here is my query below:

SELECT * FROM products
JOIN restaurants
ON (products.Venue = restaurants.name)
WHERE products.Drink_Category = Beer
AND restaurants.Area = Racks
AND (
HOUR(18:30) >= HOUR(products.Start) -- here is where the time is set
AND HOUR(18:30) <= HOUR(products.End) --and here is the end
)
ORDER BY products.Price/products.Multiple ASC;


I have as well tried
BETWEEN
query, and yet it does not work for those specific cases.

Answer

Basically, when products.End is smaller than products.Start, just add 24 to account for the day wrapping. E.g.

SELECT * FROM products
    JOIN restaurants
    ON (products.Venue = restaurants.name)
    WHERE products.Drink_Category = Beer
    AND restaurants.Area = Racks 
    AND (
    HOUR(18:30) >= HOUR(products.Start) -- here is where the time is set
    AND HOUR(18:30) <= CASE
        WHEN products.End<products.Start 
        THEN HOUR(products.End)+24 
        ELSE HOUR(products.End) END --and here is the end
    )
    ORDER BY products.Price/products.Multiple ASC;