CODEWITHSUNDEEP
phpmysqldatetimeselect
iremce
iremce

Reputation: 620

Php-MySql Query , Selecting Data According to DateTime

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..

Upvotes: 3

Views: 28694

Answers (4)

cmbuckley
cmbuckley

Reputation: 42537

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

Upvotes: 3

vfedorkov
vfedorkov

Reputation: 847

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.

Upvotes: 0

Aaron
Aaron

Reputation: 57808

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;

Upvotes: 0

Rajat Singhal
Rajat Singhal

Reputation: 11264

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..

Upvotes: 0

Related Questions