How to get data from MySQL DB between certain dates

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

Do you need to get the points in the last 7 days from current date or the last 7 days that the user got any point? — JScoobyCed
– JScoobyCed, Commented Feb 27, 2012 at 1:19
What is your date type? Are you using unix time or mysql date ? — tftd
– tftd, Commented Feb 27, 2012 at 1:22
Sorry guys. Im saving it in MySQL date/time. I want to be able to get the last 7 days from the current date using date("D");. — Bonxy
– Bonxy, Commented Feb 27, 2012 at 7:42

JoshHetland · Accepted Answer · 2012-02-27 01:37:51Z

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.

edited Feb 27, 2012 at 1:37

answered Feb 27, 2012 at 1:22

JoshHetland

1,2731 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Hopkinson · Accepted Answer · 2012-02-27 01:20:10Z

0

Something like

Select * From MyTable Where MyDate Between Date_Sub(Now(), Interval 7 Day) and Now()

answered Feb 27, 2012 at 1:20

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to get data from MySQL DB between certain dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related