How do I sum visits with an SQL query?

Question

I am trying to filter an SQL database.

Each row represents a user, the main column I’m focusing on is titled last_visited and formatted as… 2009-06-17 12:15:32.

How many users have visited in the last day/week/month?

Like in:

SELECT COUNT(*) AS USERS_TODAY
FROM parts_users
Where updated_at > (NOW()-7)

Answer 1

So is the column text or a datetime? If the former:

SELECT COUNT(*) AS USERS_TODAY 
FROM parts_users
WHERE CONVERT(datetime, updated_at, 120) > DATEADD(day, -7, GETDATE())

If the latter, omit the CONVERT.

Alternatively, you may find something like this useful:

SELECT CONVERT(datetime, LEFT(updated_at, 10), 120) AS DATE_ONLY,
       COUNT(*) AS USERS_TODAY 
FROM parts_users
WHERE CONVERT(datetime, updated_at, 120) > DATEADD(day, -7, GETDATE())
GROUP BY CONVERT(datetime, LEFT(updated_at, 10), 120)

Answer 2

http://stackoverflow.com/questions/1002639/how-to-query-multiple-sums-of-the-same-item-using-sql-in-ireport/1002660#1002660

SELECT
  SUM(case when (NOW()-1) <= datetime then 1 else 0 end) as 'today',
  SUM(case when (NOW()-7) <= datetime then 1 else 0 end) as 'thisweek',
  COUNT(*) as 'thismonth'
FROM
  table
WHERE
  (NOW()-30) <= datetime AND datetime <= NOW()