Php-MySql Query , Selecting Data According to DateTime
24,087
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
Author by
iremce
Updated on July 28, 2022Comments
-
iremce almost 2 years
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 isdate
)I hope you will help me..