php - 使用不带时间的 DATETIME 的 MYSQL SELECT 语句

Question

这非常有效:

SELECT DISTINCT customers 
FROM customertable 
WHERE time_paid BETWEEN '2016-06-28 10:27:55' AND '2016-07-05 14:07:51';

但是，由于我使用 javascript datepicker 让用户选择日期期间并且只有日期但没有时间，这显然不起作用:

SELECT DISTINCT customers 
FROM customertable 
WHERE time_paid BETWEEN '2016-06-28' AND '2016-07-05';

我的选择语句是这样的:$t1 是较低日期，而 $t2 是较高日期。

$sql = "SELECT DISTINCT customertable FROM customer WHERE time_paid BETWEEN '$t1' AND '$t2'";

$result = mysqli_query($conn, $sql);
$rowcount = mysqli_num_rows($result);

有没有办法可以在我的 select 语句中插入一个串联日期，例如 '$t1'.12.00.00，以处理完整的 DATETIME 条目选择时在我的数据库上？

Answer 1

使用mysql函数添加日期

mysql> SELECT '2008-01-02';
+------------+
| 2008-01-02 |
+------------+
| 2008-01-02 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 0 HOUR);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 0 HOUR) |
+----------------------------------------+
| 2008-01-02 00:00:00                    |
+----------------------------------------+
1 row in set (0.00 sec)


SELECT DISTINCT customertable FROM customer WHERE time_paid BETWEEN ADDDATE('$t1',INTERVAL 0 HOUR) AND ADDDATE('$t2',INTERVAL '23:59' HOUR_MINUTE)";