Make SELECT of an hour between to hours that are in 2 different columns

Question

I have the following table. I would like to make a SELECT of only the rows where the hour 11:35 is between starthour and endhour.

TABLE NAME USER
     id, name, starthour(DATE), endhour(DATE)
     1, "mariano", 10:35:00, 13:00:00
     2, "patrick", 17:35:00, 19:00:00
     3, "gloria", 21:22:00, 22:00:00
     4, "david", 22:29:00, 23:33:00

If tried doing the following SELECT.

SELECT id, name, starthour, endhour FROM user WHERE (11:59 BETWEEN starthour AND endhour);

starthour and endhour are DATE objects, not TIME objects? You could normalize that down with a modulus, but it is certainly more complex to do it in that manner. — John Green, Feb 10 '15 at 19:06

score 2 · Answer 1 · edited Feb 10 '15 at 19:16

2

I'd just use greater than and less than, like this:

SELECT id, name, starthour, endhour 
FROM user 
WHERE starthour < 11:35:00 AND endhour > 11:35:00;

...and, yes, you probably mean to use the TIME data object? You can compare DATE similarly but just adjust the data format.

edited Feb 10 '15 at 19:16

Tom

6,593
3
21
42

answered Feb 10 '15 at 19:11

Nebula42

71
4

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Actually, I am not sure about your starthour and endhour fields, which are DATE types. Would not you like to use TIME?

If not here is possible duplicate for Date: SQL query to select dates between two dates

You should use TIME(starthour) or EXTRACT() function http://www.w3schools.com/sql/func_extract.asp, so the result will be if you use TIME() function which extract time from the expression which is passed:

SELECT id, name, starthour, endhour FROM user WHERE (TIME('11:59:00') BETWEEN TIME(starthour) AND TIME(endhour));

Try this, i think it should work.

score 1 · Answer 3 · answered Feb 10 '15 at 19:26

It seems you're trying to do time queries on a date column. Assuming that start and end hour are actual datetime columns, this should do the trick.

SELECT id, name, starthour, endhour 
FROM user WHERE TIME(starthour) <= '11:35:00' 
AND TIME(endhour) <= '11:35:00'

It should provide records for '11:35:00' regardless of the day. If you're not using datetime then starthour(DATE), endhour(DATE) should be starthour(TIME), endhour(TIME) and the query

SELECT id, name, starthour, endhour 
FROM user WHERE starthour <= '11:35:00' 
AND endhour <= '11:35:00'

ought to work.

Make SELECT of an hour between to hours that are in 2 different columns

3 Answers3