如何在某些日期之间从MySQL DB获取数据

Question

Im adding points to a db for when a user does somit on site. Alls I would like to do is get the points for the last 7 days and total them.

In my DB I have it saved like: PointsID, PointsUserID, PointsTotal, PointsDate

I guess I just need to figure out the latest date in the db, then minus 7 days, then get the values from between them. Once I have returned the values they will need to be summed so I can output one number.

Thanks, Bonxy

Answer 1

SELECT SUM(PointsTotal) FROM TableName WHERE PointsUserID = 'IdInQuestion' AND PointsDate >= Date_Sub(Now(), Interval 7 Day)

In the event that you are storing the date as a unix timestamp value (AKA a PHP date value) you would want to convert the one of the two values in the appropriate direction.

for instance you could convert the field to a MySQL datetime value by replacing PointsDate with FROM_UNIXTIME(PointsDate). You could also go the other way and convert the results of DATE_SUB() by wrapping it all in a UNIX_TIMESTAMP(). Both should have equal results.