CODEWITHSUNDEEP
mysqlsqldateselectgroup-by
Akash Kumar
Akash Kumar

Reputation: 642

match timestamp with date in MYSQL using PHP

I have a table

id user     Visitor timestamp
13 username abc     2014-01-16 15:01:44

I have to 'Count' total visitors for a 'User' for last seven days group by date(not timestamp)

SELECT count(*) from tableA WHERE user=username GROUPBY __How to do it__ LIMIT for last seven day from today.

If any day no visitor came so, no row would be there so it should show 0.

What would be correct QUERY?

Upvotes: 2

Views: 1114

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT DATE(a.timestamp), COUNT(*) 
FROM tableA a 
WHERE a.user='username' AND DATEDIFF(NOW(), DATE(a.timestamp)) <= 7
GROUP BY DATE(a.timestamp);

Upvotes: 4

BlitZ
BlitZ

Reputation: 12168

There is no need to GROUP BY resultset, you need to count visits for a week (with unspecified user). Try this:

SELECT
    COUNT(*)
FROM
    `table`
WHERE
    `timestamp` >= (NOW() - INTERVAL 7 DAY);

If you need to track visits for a specified user, then try this:

SELECT
    DATE(`timestamp`) as `date`,
    COUNT(*) as `count`
FROM
    `table`
WHERE
    (`timestamp` >= (NOW() - INTERVAL 7 DAY))
    AND
    (`user` = 'username')
GROUP BY
    `date`;

MySQL DATE() function reference.

Upvotes: 6

Benyamin
Benyamin

Reputation: 54

i think it's work :)

SELECT Count(*) 
from table A 
WHERE user = username AND DATEDIFF(NOW(),timestamp)<=7

Upvotes: 0

Related Questions