Php-MySql 查询，根据日期时间选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8717492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Php-MySql Query , Selecting Data According to DateTime
提问by iremce
I would like to get number of rows that have same date. But one second must be negligible.
我想获得具有相同日期的行数。但是一秒必须可以忽略不计。
For example ; 2012-01-03 13:12:28 and 2012-01-03 13:12:27 should be perceived as the same.
例如 ; 2012-01-03 13:12:28 和 2012-01-03 13:12:27 应该被认为是相同的。
( I have a table name is myTableand the datetime column name is date)
（我有一个表名是myTable，日期时间列名是date）
I hope you will help me..
我希望你能帮助我..
采纳答案by cmbuckley
It's as simple as:
这很简单：
SELECT * FROM myTable WHERE date >= '2012-01-03 13:12:27' AND date <= '2012-01-03 13:12:28';
If you want to build it up from a variable, you could do this:
如果你想从一个变量构建它，你可以这样做：
$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();
EDIT: As per your comment below, the following SQL should return what you're after. Note that if a date contains '2012-01-03 13:12:28' it will be counted for both :27 and :28.
编辑：根据您在下面的评论，以下 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
回答by Rajat Singhal
SELECT p1.date, p1.sex, p2.date, p2.sex, p1.species ......
FROM table_name AS p1, table_name AS p2
WHERE p1.date-p2.species in(0,1);
For such cases we use self join of a table with aliases...and as your condition is the difference between the dates should not be more than 1 here it goes...
对于这种情况，我们使用带有别名的表的自连接......并且由于您的条件是日期之间的差异不应超过 1，所以它继续......
I presume that you have stored date as mysql datetime...to perform "-" operation..
我假设您已将日期存储为 mysql datetime...以执行“-”操作..
回答by Aaron
Try this once:
试试这个：
SELECT count(*)
FROM myTable
WHERE DATE_FORMAT(date, '%Y-%m-%d %H:%i') = '2012-01-03 13:12';
Or for a group-related COUNT:
或者对于与组相关的 COUNT：
SELECT DATE_FORMAT(date, '%Y-%m-%d %H:%i') as myDate, count(*)
FROM myTable
GROUP BY myDate;
回答by vfedorkov
If you only need to count number of rows per date it would be simple GROUP BY:
如果您只需要计算每个日期的行数，则很简单 GROUP BY：
SELECT COUNT(*) as cnt, DATE_FORMAT(date, '%Y-%m-%d') as my_date
FROM myTable
GROUP BY my_date
But by your question you are not only need to count rows within a date, but also count not a single events but it's series. In this case you will need to define what exactly series are. Say two events within one second should be counted as one. What if there are four events with 0.7 distance between each other? In this case I would suggest to count series in two steps.
但是根据您的问题，您不仅需要计算日期中的行数，而且还需要计算不是单个事件而是系列。在这种情况下，您需要定义系列到底是什么。说一秒钟内的两个事件应该算作一个。如果有四个事件，彼此之间的距离为 0.7 会怎样？在这种情况下，我建议分两步计算系列。
First - order events by time:
按时间排序的一阶事件：
SELECT * FROM myTable WHERE date >= XXXX and date < YYYY ORDER BY date ASC
Then on the application side you can count the gap between two records and increase counter if distance between two close events is more than one second.
然后在应用程序方面，您可以计算两个记录之间的间隔，如果两个关闭事件之间的距离超过一秒，则增加计数器。
Don't forget to add index on datefield to speed up this query.
不要忘记在date字段上添加索引以加速此查询。
