Curdate() minus 7 days in MySQL5

i have a table that stores all my users, including a column “created” with a unix timestamp of when the account was created. im now trying to run a query that tells me how many users were created in the past week. the MySQL5 query below doesnt give any errors but returns all my users. obviously i dont have the “in the past 7 days” part right yet.

anyone?

select count(distinct uid) from users where created > (select date_sub(curdate(), interval 7 day) )

ref: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

if your “created” column is a unix timestamp, you have to convert it to a mysql datetime value first with the from_unixtime():

select count(distinct uid)
  from users
 where from_unixtime(created) > date_sub(curdate(), interval 7 day)

yep, thanks!!

i would like to make a suggestion for improvement

change this –

where from_unixtime(created) > date_sub(curdate(), interval 7 day)

to this –

where created > unix_timestamp(current_date - interval 7 day)

reason: efficient use of an index on the created column

ah, good call

you could also probably use COUNT(uid) instead of COUNT(DISTINCT uid)

in general, counting distinct values is far more expensive in computing resources than counting values

in the case of a table which has only one row per column value–such as would happen if you were counting the values of a primary key like the uid in a users table–the distinct count would be the same as the count

maybe the optimizer is smart enough to detect that, maybe it isn’t

just another example where it often falls upon us to write the more efficient SQL

top advise from the SP mysql pros, thanks again!