AeJey AeJey - 3 months ago 9
SQL Question

Retrieve Rows if Current Time is in between the Start and End Time in Rows

If I run the query

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name;


I get the following result


10:30:50 | 1:00:00 | 12:00:00

10:30:50 | 1:00:00 | 12:00:00


But when I try to retrieve rows if current time is in between the START_HOUR and END_HOUR (
the START_HOUR and END_HOUR are INT(2)
) It returns 0 rows.

I tried the following queries and none worked

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name WHERE curtime() >=concat(START_HOUR,':00:00') && curtime()<=concat(END_HOUR,':00:00');

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name WHERE curtime() BETWEEN concat(START_HOUR,':00:00') AND concat(END_HOUR,':00:00');

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name WHERE concat(curtime(),'') >=concat(START_HOUR,':00:00') && concat(curtime(),'')<=concat(END_HOUR,':00:00');

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name WHERE concat(curtime(),'') BETWEEN concat(START_HOUR,':00:00') AND concat(END_HOUR,':00:00');

SELECT curtime(), concat(START_HOUR,':00:00'), concat(END_HOUR,':00:00') from table_name WHERE CONCAT(DATE_FORMAT(NOW(), '%H:%i'), ':00') >=concat(START_HOUR,':00:00') && CONCAT(DATE_FORMAT(NOW(), '%H:%i'), ':00')<=concat(END_HOUR,':00:00');


and all the combinations of these.

Please help me find out what I am doing wrong and how I can retrieve the desired records.

Answer

You need to CAST the time values into TIME types.

SELECT 
    curtime(), 
    CAST(concat(START_HOUR,':00:00') as TIME), 
    CAST(concat(END_HOUR,':00:00') as TIME)
FROM table_name WHERE 
    CURTIME() BETWEEN 
        CAST(CONCAT(START_HOUR,":00:00") AS TIME) AND 
        CAST(CONCAT(END_HOUR,":00:00") AS TIME)