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.
( I have a table name is myTable and the datetime column name is date )
I hope you will help me..
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.
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
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...
I presume that you have stored date as mysql datetime...to perform "-" operation..
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:
SELECT DATE_FORMAT(date, '%Y-%m-%d %H:%i') as myDate, count(*)
FROM myTable
GROUP BY myDate;
WHERE DATE('2012-01-03 01:01:01') = DATE(my_date) for every row that is on 2012 01 03.
WHERE DATE(NOW()) = myDate to check, for instance, for today, or with DATE_ADD, for x days from today - just on a sidenote ,)If you only need to count number of rows per date it would be simple 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.
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 date field to speed up this query.
