Level 33
[DB Fiddle link] https://www.db-fiddle.com/f/9ZbuD7aCTpf8sXfeRd2anY/0
Jun 19, 2020
4
Level 33
How to query result form given time range with MYSQL
I want to filter teams date where given time matches with team match time we are using IST timezone on frontend and UTC for MYSQL side
create table teams (
team_name varchar(255),
match_start_time time,
match_end_time time
);
insert into teams values
("a", "18:30", "18:29"),
("b", "18:30", "18:29"),
("c", "18:30", "18:29"),
("d", "18:30", "18:29"),
("e", "21:30", "18:29"),
("f", "19:30", "18:29"),
("g", "22:00", "00:30"),
("h", "23:30", "00:30"),
("1", "19:30", "18:29"),
("j", "21:30", "13:30"),
("k", "01:00", "04:00"),
("l", "09:30", "18:30")
SELECT team_name,
TIME_FORMAT(match_start_time, "%H:%i") start_time,TIME_FORMAT(match_end_time, "%H:%i") end_time FROM teams
WHERE
match_start_time >= CAST('18:30:00' AS TIME)
AND match_end_time <= CAST('21:30:00' AS TIME);
SELECT team_name,
TIME_FORMAT(match_start_time, "%H:%i") start_time,TIME_FORMAT(match_end_time, "%H:%i") end_time FROM teams
WHERE
match_start_time <= CAST('18:30:00' AS TIME)
AND match_end_time <= CAST('21:30:00' AS TIME);
here frontend send time range 00:00 to 03:00 which in UTC 18:30 to 21:30
but above query I am getting wrong result for query 1 result returns team h but team h match end time is 00:30 which is greater than 21:30
for query 2 result returns team k but team k match start time is 01:00 in IST it will be 06:30 but out input range is 00:00 to 03:00 so it is wrong again
so what will be the correct approach ?
Level 7
I recommend you to use https://carbon.nesbot.com/docs will helps you to cast the date and time as you want.
Level 33
@psylogic Sorry but I want solution for sql query for PHP side we already have things in place.
Level 29
The query returns the correct result as any value in (00:00 to 21:30) is <= "21:30".
Maybe the data types for both columns should be DateTime?
Or you might need to do something like
$sub_query ="";
if($match_end_time > $match_start_time){
$sub_query = " AND match_end_time > match_start_time";
}
$query1.=$sub_query;
Please or to participate in this conversation.