Retrieving Count of Posts from Last Month

[Gossamer]

I'm trying to write a query to get the number of posts from each user from the last month, but this isn't pulling up any results. I know there's something wrong with my WHERE statement, but I haven't found something that works yet. I don't want to hard-code in dates to check between, because I want to pull up this information regularly over time. My goal is to make these subqueries of a larger query that will show posting #s by month over the past year.

SELECT user_id,
username,
COUNT(post_id)

FROM xf_post

WHERE post_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH)

GROUP BY user_id

ORDER BY username

Any help would be appreciated. Thank you!

 

[Anomandaris]

Necroing this with a solution hoping it helps someone

The reason it doesn't work is because post_date is in EPOCH unix timestamp format. and the DATE functionality of SQL is not.

To select all posts from the past month you can use 2629743 which is the number of seconds in a month.

SELECT * FROM xf_post WHERE post_date > (UNIX_TIMESTAMP() - 2629743)

If you want to do this using date strings you can convert them to epoch using UNIX_TIMESTAMP() to convert them:

SELECT * FROM xf_post WHERE post_date > (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)));