Rewriting MySQL date functions for SQLite

Question

Please help me rewrite this query from MySQL to SQLite format:

SELECT MAX(pubdate) as pubdate, 
COUNT(DISTINCT post_id) AS count, 
DATE_FORMAT(pubdate,"%b-%y") as month 
FROM posts 
WHERE pubdate >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH) 
GROUP BY month 
ORDER BY pubdate ASC

Basically, I want to get the count of all posts for the last 5 months grouped by the month, but I have difficulties rewriting DATE_FORMAT(...) and DATE_SUB(...) functions.

pubdate has the standard MySQL TIMESTAMP format (2011-06-21 20:05:01)

Answer 1

SELECT MAX(pubdate) as pubdate, 
COUNT(DISTINCT post_id) AS count, 
strftime('%m-%Y',pubdate) as month
FROM posts 
WHERE pubdate >= datetime('now','-5 month')
GROUP BY month 
ORDER BY pubdate ASC

Is the closest that I can make it clean-looking. However, this will return '01-1999' format for month. If you want it to be 'Jan 99' you'll have to have some form of messy case statement.

I don't have access to sqlite at the moment, so this code has not been tested.