search for overlaping booking times

[GarryBrown]

Hello,
I've got the following problem:

My table contains 5 rows including two rooms beeing booked several having different booking start time and booking finish time and date booked.

1 )Room code = AS1, start time (char) = 1800, finish time (char) = 1900, bookingDate (date) = '1-MAR-2013'
2) Room code = AS1, start time (char) = 1400, finish time (char) = 2000, bookingDate (date) = '1-MAR-2013'
3) Room code = AS1, start time (char) = 2000, finish time (char) = 2200, bookingDate (date) = '1-MAR-2013'
4) Room code = AS2, start time (char) = 1800, finish time (char) = 2100, bookingDate (date) = '1-MAR-2013'
5) Room code = AS2, start time (char) = 2200, finish time (char) = 2300, bookingDate (date) = '1-MAR-2013'

This data is wrong according to database implementation and is only inserted for producing the following query:
Basically i need to write a SQL query that will return the rooms that have overlaping booking times.
So the output must be 1 and 2 lines as their start and finish times overlap. Can anyone please tell me how this can be done?
Thanks in advance.

[NogDog]

If you can change the start/end time fields to be TIME instead of CHAR (or even DATETIME or TIMESTAMP), and have the end_time be something like 18:59 (or 18:59.59) instead of 19:00, you could maybe do something like

select * from the_table as t1
inner join the_table as t2 on t1.room_code = t2.room_code
where
    t1.start_time between t2.start_time and t2.end_time
or
    t1.end_time between t2.start_time and t2.end_time

[GarryBrown]

NotDog,
Thanks very much for your reply. I've already tried exactly the same code and some versions of it, but it does not work. The problem is that I am given already this scenario so I am not allowed to change the date type.

[NogDog]

I suppose you could cast them to integer and subtract 1 from the end_time, perhaps? Oh, and I just realized you'd need to add a check in the where clause that the records are for the same date.

select * from the_table as t1
inner join the_table as t2 on t1.room_code = t2.room_code
where
    t1.booking_date = t2.booking_date
and (
    cast(t1.start_time as unsigned) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
or
    (cast(t1.end_time as unsigned) - 1) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
)

However, the downside of this is that it will require scanning of all records where the booking dates match, since it will have to cast every start/end time, and thus cannot take advantage of any indexes on those fields, I believe.