MySQL Select : where time is greater then and less

问题:

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?

回答1:

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).

回答2:

The reason is because you are extracting the HOUR and comparing with the time, you need to cast the time part Try your query as::

SELECT 

* 
FROM cdr_table 
WHERE 
OwnerUserID ='$_SESSION[user_id]' 
AND GatewayID = $gateway_id 
AND DATE(Dialed) = $date_sql 
AND Dialed != 0 
AND DATE_FORMAT(StartTime,'%r') BETWEEN ('$start_time') AND ('$end_time')