Mysql daily downloads counter

Question

I am loosing it a bit and cant think of this sql correctly

I have

tables: userid , counter , date and

I need to check how many files has user downloaded in last 24h , starting sql

SELECT SUM(counter) FROM download_log WHERE userid = 258 AND date = DATE(NOW())

but it is returning null , and I downloaded min 40 files ,
date format in date table is

2012-05-05 01:59:55

I also tried , date = NOW()

same thing what am i missing ?

thank you!

similar post: http://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql — Mohamed Nuur, Jul 26 '12 at 23:28

score 1 · Answer 1 · answered Jul 26 '12 at 23:28

1

Try something like this:

SELECT SUM(counter) FROM download_log WHERE userid = 258 AND date = CURDATE()

That should work.

answered Jul 26 '12 at 23:28

Mohamed Nuur

currdate works in this combo only AND DATE( `date` ) = CURDATE( ) , thnx for this – Benn Jul 26 '12 at 23:41

score 1 · Answer 2 · answered Jul 26 '12 at 23:28

1

I'd guess you are storing date and time in column named date , so it should work :

SELECT SUM(counter) FROM download_log WHERE userid = 258 AND 
DATE(`date`)= DATE(NOW())

answered Jul 26 '12 at 23:28

a1ex07

this works perfect but I am wondering , this should be 24h right? – Benn Jul 26 '12 at 23:40
That doesn't actually return #files downloaded in last 24 hours ; it returns #files downloaded today (it may or may not be what you want). If you need exactly 24 hours from now, it should be `WHERE date > NOW() - INTERVAL 24 HOUR AND date < NOW()`. Also, "date" should be in backticks. – a1ex07 Jul 26 '12 at 23:44

score 1 · Accepted Answer · answered Jul 26 '12 at 23:41

1

Wouldn't it be:

SELECT SUM(counter) FROM download_log where userid = 258 and date >= DATE_SUB(NOW(), INTERVAL 24 HOUR);

You have to get all rows within the last 24 hours.

answered Jul 26 '12 at 23:41

sceaj

i think you on th right way but im getting error in your SQL syntax; – Benn Jul 26 '12 at 23:44
I fixed a typo, function should be DATE_SUB, not DATE_SUM. – sceaj Jul 26 '12 at 23:45

3 Answers3