Php-MySql查询，根据DateTime选择数据

Question

我想获取具有相同日期的行数。但是一秒钟肯定可以忽略不计。

例如; 2012-01-03 13:12:28 和 2012-01-03 13:12:27 应该被视为相同。

(我有一个表名是myTable，日期时间列名是date)

我希望你能帮助我..

Answer 1

就这么简单:

SELECT * FROM myTable WHERE date >= '2012-01-03 13:12:27' AND date <= '2012-01-03 13:12:28';

如果你想从一个变量构建它，你可以这样做:

$date = '2012-01-03 13:12:27';
$timestamp = strtotime($date);
$mysqli = new mysqli();

// Note: allowing 1 second either side, this is up to you
$lower = date('Y-m-d H:i:s', $timestamp - 1);
$upper = date('Y-m-d H:i:s', $timestamp + 1);

$stmt = $mysqli->prepare('SELECT * FROM myTable WHERE date >= ? AND date <= ?');
$stmt->bind_param('ss', $lower, $upper);
$stmt->execute();

编辑:根据您在下面的评论，以下 SQL 应该返回您所追求的内容。请注意，如果日期包含“2012-01-03 13:12:28”，则 :27 和 :28 都将被计算在内。

SELECT
    upper_date,
    COALESCE(lower_count, 0) + upper_count
FROM
    (SELECT
        date AS upper_date,
        COUNT(1) AS upper_count
    FROM myTable
    GROUP BY date
    ) AS upper
LEFT JOIN
    (SELECT
        date - INTERVAL 1 SECOND AS lower_date,
        COUNT(1) AS lower_count
    FROM myTable
    GROUP BY date
    ) AS lower
ON upper.upper_date = lower.lower_date