PHP/MySQL between day and day+1d7h

本文关键字：and day+1d7h day between MySQL PHP | 更新日期: 2023-09-27

我正在尝试执行这个查询到我的php文件，但没有返回任何结果(没有错误)

$day = $_GET["day"];    
$query_string = "SELECT * FROM $tablename WHERE TIMESTAMP('date','time') BETWEEN '$day 07:01:00' AND DATE_ADD('$day',INTERVAL '1 7' DAY_HOUR) ORDER BY kod";
display_db_query($query_string, $connection, $header_bool, $table_params);
//date format: yyyy-mm-dd
//time format: hh:mm:ss

有人能帮忙吗?是否有任何错误进入$query_string?

下面是display_db_query代码:

function display_db_query($query_string, $connection, $header_bool, $table_params) {
    // perform the database query
    $result_id = mysql_query($query_string, $connection)
    or die("display_db_query:" . mysql_error());
    // find out the number of columns in result
    $column_count = mysql_num_fields($result_id)
    or die("display_db_query:" . mysql_error());
    // Here the table attributes from the $table_params variable are added
    print("<TABLE $table_params >'n");
    // optionally print a bold header at top of table
    if($header_bool) {
        print("<TR>");
        for($column_num = 0; $column_num < $column_count; $column_num++) {
            $field_name = mysql_field_name($result_id, $column_num);
            print("<TH>$field_name</TH>");
        }
        print("</TR>'n");
    }
    // print the body of the table
    while($row = mysql_fetch_row($result_id)) {
        print("<TR ALIGN=LEFT VALIGN=TOP>");
        for($column_num = 0; $column_num < $column_count; $column_num++) {
            print("<TD>$row[$column_num]</TD>'n");
        }
        print("</TR>'n");
    }
    print("</TABLE>'n"); 
}

当我手动执行查询时:

SELECT * FROM stats_temp WHERE TIMESTAMP('date','time') BETWEEN '2013-04-28 07:01:00' AND DATE_ADD('2013-04-28 00:00:00',INTERVAL '1 7' DAY_HOUR) ORDER BY kod

返回0个结果。

当我手动执行查询没有DATE_ADD但手动计算:

SELECT * FROM stats_temp WHERE TIMESTAMP('date','time') BETWEEN '2013-04-28 07:01:00' AND '2013-04-29 07:00:00' ORDER BY kod

它也返回0个结果，这是不可能的，因为有符合此条件的数据。

数据格式是正确的，所以问题在TIMESTAMP('date'，'time')。有什么建议吗?

我终于找到了解决办法。问题确实存在于query_string中，所以我尝试重写它。下面是工作查询:

$query_string = "SELECT * FROM $tablename 
    WHERE CONCAT(date,' ',time) > '$day 07:00' AND 
    CONCAT(date,' ',time) < DATE_ADD('$day 06:59',INTERVAL '1' DAY)
    ORDER BY kod";