CODEWITHSUNDEEP
mysql
MKC
MKC

Reputation: 188

WHERE clause to filter times that are under an hour

SELECT 
    name,
    start_time,
    TIME(cancelled_date) AS cancelled_time,
    TIMEDIFF(start_time, TIME(cancelled_date)) AS difference
FROM
    bookings

I'm trying to get from the database a list of bookings which were cancelled with less than an hour's notice. The start time and the cancellation times are both in TIME format, I know a timestamp would have made this easier. So above I've calculated the time difference between the two values and now need to add a WHERE clause to restrict it to only those records that have a difference of under 1:00:00. Obviously this isn't a number, it's a time, so a simple bit of maths won't do it.

Upvotes: 0

Views: 195

Answers (2)

Robert
Robert

Reputation: 2824

you can use having difference<time('1:00')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would be inclined to do this by adding and hour to the notice, something like this:

WHERE start_time > date_add(cancelled_date, interval 1 hour)

I can't quite tell what the right logic is from the question, because your column names don't match the description.

In this case, so a subtraction or doing the comparison are similar performance wise. But, if you had a constant instead of cancelled_date, then there is a difference. The following:

WHERE start_time < date_add(now(), interval -1 hour)

Allows the engine to use an index on start_time.

Upvotes: 2

Related Questions