mysql 小于日期,MySQL Select：时间大于或小于时间

I have a function that accepts two time parameters: $start_time, $end_time

each parameter is define as time in php as

$start_time = date("H:i:s",strtotime($start)); ->like "06:12:44"

$end_time = date("H:i:s",strtotime($end)); ->like "08:22:14"

I want to build a query that gives the results between these times

This is my function

function statistics_connected_hour($gateway_id , $date_sql ,$start_time ,$end_time){

$statistics_connected = mysql_query(

"SELECT *

FROM cdr_table

WHERE OwnerUserID ='$_SESSION[user_id]'

AND GatewayID = $gateway_id

AND DATE(Dialed) = $date_sql

AND Dialed != 0

AND Hour(StartTime) BETWEEN ('$start_time') AND ('$end_time')

");

return $statistics_connected;

}

StartTime Column in the DB define as "2012-12-28 13:32:28"

The query does not return any results although there are supposed to return

When I check ->

$num = mysql_num_rows($statistics_connected);

It always returns 0 in $num

Can anyone help me understand what the problem is?

解决方案

You want TIME(), not HOUR().

SELECT * FROM cdr_table

WHERE OwnerUserID = '$_SESSION[user_id]'

AND GatewayID = $gateway_id

AND DATE(Dialed) = $date_sql

AND Dialed != 0

AND TIME(StartTime) BETWEEN '$start_time' AND '$end_time'

Also, I'd strongly suggest escaping all variables you're embedding in SQL code with mysql_real_escape_string() or equivalent, even if you're sure there's nothing harmful in them, just to make it a habit.

Note that a query like this may be intrinsically inefficient, since it cannot make use of indexes on the StartTime column. If there are a lot of potentially matching rows in the table, it could be a good idea to denormalize your table by creating a separate column storing only the time part of the StartTime and setting up an index on it (possibly combined with other relevant columns).