I have a field that is a a DATETIME field.
How do I select rows that fall between between 12:00 am and 8:30 am?
when I type in this query:
$query = "select * from table where TIME(changetime) between '12:00:00 and '08:30:00'";
it gives me zero hits.
The changetime field has the date like this: 2015-07-14 10:57:57
1) you don't need TIME(changetime), it could probably worsen your search-time because mysql will need to apply a function instead of using index.
use just changetime.
2) where changetime between '2015-07-14 10:57:57' and '2015-07-14 12:57:57' http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between
3) you need to use right range - in your example END_DATE is BEFORE START_DATE. So, between will return 0 (12:00:00 > 08:30:00)
4) look at @Barmar's comment, maybe you really want entries between some time in any date, not between the datetimes
12:00:00 is 12 PM (noon), not 12 AM (midnight). You need to use
WHERE TIME(changetime) BETWEEN '00:00:00' AND '08:30:00'