SQL where two time values between two time columns

Question

With MySQL, I need to find rows where two values are between two columns.

For example, I'm creating a schedule from '15:00' to '18:00', but the query must verify if any row has an period in use. If there is a record from '14:00' to '18:30', I need to return it to validate.

I tried something like, but not works:

select * from availabilities where (('15:00' or '18:00') between start_hour and end_hour)

Any idea?

http://wiki.c2.com/?TestIfDateRangesOverlap – shawnt00 Nov 16 '16 at 18:26 — shawnt00, Nov 16 '16 at 18:26

Salman A · Accepted Answer · 2016-11-16T18:35:36.427

3

This is basically a "test if range overlaps another range" question. The following query should work as long as start time is less than end time:

SELECT *
FROM availabilities
WHERE '18:00' > start_hour AND end_hour > '15:00'

edited Nov 16 '16 at 18:35

answered Nov 16 '16 at 18:30

Salman A

262,204
82
430
521

what must be done if start time is greater than end time. – paradox May 28 '20 at 21:08

score 2 · Answer 2 · answered Nov 16 '16 at 18:20

2

You can use TIME() function to extract the time part of the datatime/timestamp column

Eg.

SELECT * FROM Table
WHERE TIME('timeStampCol') BETWEEN '12:00:00' AND '18:00:00'

answered Nov 16 '16 at 18:20

Ripon

141
1
8

http://stackoverflow.com/questions/6973844/select-records-by-time-in-interval-between-120000-and-180000-on-every-day – Ripon Nov 16 '16 at 18:26

SQL where two time values between two time columns

2 Answers2