mysql count total last week from timestamp

Question

     id  date
    1  1288006344
    2  1288010391
    3  1288010752
    4  1288011379
    5  1288013258
    6  1288014043
    7  1288014555
    8  1288015611
    9  1288019119
    10  1288020490
    11  1288023483
    12  1288029300
    13  1288031668
    14  1288032090

How to count total last 7 days?

Thank you !

Answer 1

something like this should do the trick:

SELECT count(*) FROM your_table_name 
WHERE 
  date_sub(curdate(), INTERVAL 7 DAY) <= date;

That will select dates that are after 7-days ago. If you want to exclude any dates that are in the future, you'll have to add a clause for that:

SELECT count(*) FROM your_table_name 
WHERE 
  date_sub(curdate(), INTERVAL 7 DAY) <= date
  AND NOW() >= date;

more info on the various mysql date functions is available at the mysql documentation site.

Answer 2

SELECT SUM(mycolumn) AS TOTAL
  FROM mytable
 WHERE FROM_UNIXTIME(mybigint_column_containing_unixtimestamps) >= 
       DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 7 DAY)