Help with a query?

[successguy]

I'm wondering if anybody would be so kind as to tell me how to do this query. I want to know every member who has posted on a given day sorted in order of number of posts they made that day. So if for example I asked who posted on February 21, 2012, it would show me everybody who posted on that day. And the sorting would be done not by the member's total posts, but by their total posts for that day only. You can basically already do this in the search engine, but you can sort by total posts only.

[kh99]

Try this:

SELECT userid, username, COUNT(*) as count FROM post 
WHERE visible=1 AND 
UNIX_TIMESTAMP('2012-02-22') < dateline AND
UNIX_TIMESTAMP('2012-02-23') >= dateline
GROUP BY userid ORDER BY count DESC

I tried to come up with one that let you just enter the date once, but I'm not that familiar with the mysql functions. On the plus side, this lets you find posts between any two dates.

[successguy]

Thanks so much, really appreciate it!

[nhawk]

You were on the right track...

The way you have it, the timestamp will contain the time of 00:00:00 so this will give only the date chosen. (between 2012-02-22 00:00:00 and 2012-02-22 11:59:59)

SELECT userid, username, COUNT(*) as count FROM post 
WHERE visible=1 AND 
UNIX_TIMESTAMP('2012-02-22') >= dateline AND
UNIX_TIMESTAMP('2012-02-23') < dateline
GROUP BY userid ORDER BY count DESC

[kh99]

Originally Posted by nhawk

You were on the right track...

The timestamp will contain the hour of 00:00:00 so this will give only the date chosen. (between 2012-02-22 00:00:00 and 2012-02-22 11:59:59)

SELECT userid, username, COUNT(*) as count FROM post 
WHERE visible=1 AND 
UNIX_TIMESTAMP('2012-02-22') >= dateline AND
UNIX_TIMESTAMP('2012-02-23') < dateline
GROUP BY userid ORDER BY count DESC

That doesn't work, you've got the comparisons backward. What I meant was I tried to think of a way where, if you wanted to change the date you'd only have to change it in one place instead of two.

[nhawk]

You're right, a little brain freeze here...

should be this to account for 00:00:00 time..

SELECT userid, username, COUNT(*) as count FROM post 
WHERE visible=1 AND 
dateline >= UNIX_TIMESTAMP('2012-02-22') AND
dateline < UNIX_TIMESTAMP('2012-02-23')
GROUP BY userid ORDER BY count DESC

Because dateline will be greater than or equal to unix timestamp of '2012-02-22 00:00:00'.

And dateline will be less than a unix timestamp of '2012-02-23 00:00:00'.

[kh99]

Well, that's equivalent to what I had except for where you put the '=', but fair enough, it belongs on the lower date to have the query results match the posts with the given date in the dateline. But it would only affect posts that were posted exactly at midnight (to the second).