两个datetime列之间的Mysql [英] Mysql between two datetime columns

问题描述

我有以下代码，似乎没有产生预期的结果：

I have the following code and it doesn't seem to be generating the expected results:

$date_from = '2012-04-27 18:19:33';
$date_to = '2012-05-29 00:59:57';

$database->connect(); 
        $ancestors = mysql_query('
            SELECT * FROM ' . $database->db_prefix . 'comments e
                WHERE e.created BETWEEN "' . $date_from . '" AND "' . $date_to . '" AND   
                    e.ancestors = "' . $comment["id"] . '" AND e.user_id != "' . $user->user_object["id"] . '" AND
                        NOT EXISTS
                        (
                            SELECT null 
                            FROM ' . $database->db_prefix . 'notifications d
                            WHERE  d.target_id = e.id
                        )
                        ORDER BY e.created DESC
        ', $database->connection_handle);
    $database->close();

这可能是一个愚蠢的错误..但是我很难确定，当我' m不确定选择语句BETWEEN和正在使用的日期是否是有效的MYSQL的东西。

It may be a silly error.. But I'm finding it hard to be sure when i'm unsure if the select statement BETWEEN and the dates being used are valid MYSQL things.

任何帮助将是伟大的。我一直在冲刷，我在做什么似乎是正确的，从而造成混乱。

Any help would be great. I have been scouring and what I am doing seems to be correct thus the confusion.

EDITED //

EDITED //

我在date_from和date_to日期之间有很多意见，在通知表中没有任何通知（不存在的部分）...所以基本上，我应该得到大约25条评论返回...而是我只收到一个返回的评论，日期2012-05-29 00:23:39

I have many comments made between the date_from and date_to dates and there are no notifications in the notifications table (not exists part)... So basically, I should get about 25 comments back... Instead I just get one comment returned which has a date of 2012-05-29 00:23:39

EDITED //

EDITED //

是因为你只能比较一个日期到一个日期..一个时间到一个时间？为了做到这一点，我该如何调整mysql？

Is it because you can only compare a date to a date.. and a time to a time? How might I adapt the mysql in order to do this?

EDITED //

EDITED //

出错

1. 您可以在两个日期之间返回记录吗？


2. 你能举个例子吗？

EDITED //
问题已经解决了。这与查询无关，所以安全地说这是正确的方法。我以一个笨蛋的方式完成了我的日期范围。谢谢你们所有的呃帮助家伙!!!!

EDITED // Problem has been solved. It was nothing to do with the query so its safe to say this is the correct way of doing it. I had done my date ranges in a bonkers way. Thanks for all ur freaking help guys!!!!

推荐答案

我遇到了同样的问题，我想你可能想应用这个解决方案：

I met the same problem and I think you may want to apply this solution:

$from = $_GET['from'];
$to = $_GET['to'];
include("connect.php");
$link=Connection();
$query = "SELECT * FROM records WHERE date_time BETWEEN '$from' AND '$to'";
$result=mysql_query($query,$link);
$list = array();
if($result === FALSE) {
    echo "No data";
}

这里的诀窍是签名引用，您需要将变量放在引用让mysql处理它

The trick here is the signle quote, you need to put your variable inside that quote to let mysql handle it

这篇关于两个datetime列之间的Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！