Compare MySQL "Time" Field with String in PHP

Question

Hello,

I have a specific row in my database, with the following information:

    data:

     user_id     start_time        end_time
       405        12:00:00         14:00:00

I have the following SELECT statement:

       SELECT *
         FROM data
         INNER join users on users.user_id = data.user_id
          WHERE NOT EXISTS 
           (SELECT * FROM data 
            WHERE '10:00:00' BETWEEN start_time AND end_time)
            GROUP BY users.usrID");

Now, I would think that this query would return the above row (user 405), however, it does not return anybody. (Because 10:00:00 is not between 12:00:00 and 14:00:00).

Interestingly, if I change the value 10:00:00 to 07:00:00, it works fine.

Any ideas on why this is not working? In the database, I have the fields set up as TIME, and they're formatted as such: 00:00:00

Thanks very much!

Update:

I changed my query around to look like this:

SELECT usrID, 
       first, 
       last
  FROM users
 WHERE user_id NOT IN (SELECT u.user_id
                         FROM `data` d, 
                              `users` u
                        WHERE d.user_id = u.user_id 
                          AND CAST('10:00:00' AS TIME) BETWEEN start_time AND end_time)
ORDER BY u.user_id

And this seemed to do the trick. Thanks for all the help.

Answer 1

Per the MySQL Manual page for between:

"For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE."

Try:

SELECT *
FROM data
    INNER join users on users.user_id = data.user_id
WHERE NOT EXISTS 
    (
     SELECT * 
     FROM   data 
     WHERE  CAST('10:00:00' AS TIME) BETWEEN start_time AND end_time
    )
GROUP BY users.usrID

Without the explicit typecast, MySQL may be converting the time values to string literals for comparison, rather than converting the string literal to a time value.

Answer 2

I think what's happening is that the time '10:00:00' is being taken as a string, rather than a time. That would explain why the '07:00:00' gives different results. Try casting the value to a time...I think the syntax is something like:

cast('10:00:00' as time)

Answer 3

Try:

  SELECT *
    FROM DATA d
    JOIN USERS u ON u.user_id = d.user_id
   WHERE NOT EXISTS (SELECT NULL 
                       FROM DATA t
                      WHERE t.id = d.id
                        AND CONVERT('10:00:00', TIME) BETWEEN t.start_time AND t.end_time)
GROUP BY u.usrID

When you get the BETWEEN evaluating properly, you'd notice that you aren't correlating the subquery - just because they're the same table, doesn't mean the two queries are linked.