CODEWITHSUNDEEP
mysqlsqlwhere-clausebetween
user3474451
user3474451

Reputation: 47

MySQL select where row content is between two hours

I have this row:

How it's possible to get only the results between 07:00 and 00:00? 07:00, 23:59 and 00:00 in my case.

$query = "SELECT * FROM `".$today."` WHERE hour BETWEEN '07:00' AND '00:00'";

... returns nothing.

Upvotes: 1

Views: 4633

Answers (4)

chetan
chetan

Reputation: 2896

select * 
from tab 
where replace(hour,':','') 
between '0700' AND '2359'
OR replace(hour,':','') ='0000';

Upvotes: 0

stmfunk
stmfunk

Reputation: 683

I think it should be more like 

SELECT * FROM `".$today."` WHERE hour >= "7:00" AND hour <= "24:00";

This worked for me anyway.

Upvotes: -1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

Date stored as varchar makes it very difficult for query comparison and should be stored in proper datattype.

The workaround in your case i.e. using varchar stored time you can use something as

select * from 
table_name 
where 
TIME_FORMAT ( 
   str_to_date ( 
       replace (hour,':',''),'%H%i'
   ),'%H:%i'
) 
between '07:00' AND '23:59';

DEMO

Upvotes: 2

JariOtranen
JariOtranen

Reputation: 126

In general it seems a bit fuzzy problem/data structure setup as you you need to be careful if you refer to 07:00 -> 00:00, or 00:00 -> 07:00.

Working with TIMESTAMP will probably save a lot of headache, for example:

CREATE TABLE test(time1 TIMESTAMP, data VARCHAR(20));
INSERT INTO test(time1, data) VALUES('2014-05-09 07:00', 'kala1');
INSERT INTO test(time1, data) VALUES('2014-05-09 06:43', 'kala2');
INSERT INTO test(time1, data) VALUES('2014-05-09 23:59', 'kala3');
INSERT INTO test(time1, data) VALUES('2014-05-10 00:00', 'kala4');
INSERT INTO test(time1, data) VALUES('2014-05-10 00:40', 'kala5');

Then you can do something like this

SELECT * FROM test WHERE time1 >= '2014-05-09 07:00' AND time1 <= '2014-05-10 00:00'

Upvotes: 0

Related Questions