MySQL Select:时间大于或小于时间 [英] MySQL Select : where time is greater then and less than time

问题描述

我有一个接受两个时间参数的函数:$start_time，$end_time 每个参数在php中定义为时间

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列定义为"2012-12-28 13:32:28"

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

它总是在$ num中返回0

It always returns 0 in $num

任何人都可以帮助我了解问题所在吗?

Can anyone help me understand what the problem is?

推荐答案

您要此外，我强烈建议 使用 mysql_real_escape_string() 或同等学历，即使您确定它们中没有有害物质，也只是养成一种习惯.

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.

请注意，这样的查询可能本质上效率低下，因为它无法利用StartTime列上的索引.如果表中有很多可能匹配的行，则可以通过创建单独的列来存储表的非规范化，这是一个单独的列，该列仅存储的时间部分 并设置索引(可能与其他相关列结合使用).

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

这篇关于MySQL Select:时间大于或小于时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！