重写MySQL的日期函数以适用于SQLite

Question

请帮忙将以下 MySQL 查询语句转换为 SQLite 格式：

请帮我将此查询从 MySQL 转换为 SQLite 格式：

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

基本上，我想获得最近5个月每月所有帖子的计数，但我在重写DATE_FORMAT(...)和DATE_SUB(...)函数时遇到了困难。

pubdate具有标准的MySQL TIMESTAMP格式（2011-06-21 20:05:01）

- Tony

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

这是最接近干净的写法了。但是，对于month，它会返回“01-1999”格式。如果你想要“Jan 99”的格式，你需要使用一些冗长的条件语句。

此代码未经过SQLite测试，因为我目前没有访问SQLite的权限。

- Sam DeHaan

---

嗨，Sam，感谢您的回复。我尝试了您的查询，但奇怪的是它返回了所有月份，而不是最后5个月。我尝试将“-5 month”更改为“-5 months”，但仍然返回_所有_月份。当然，我需要自当前日期起的最后5个月，因此将WHERE部分更改为WHERE pubdate >= datetime('now' , '-5 months')可以正确返回最后5个月。但既然您提到了它，我在想，为什么datetime(pubdate,'-5 months')没有效果呢？ - Tony

Sam: 但是在这种情况下 WHERE pubdate >= datetime('now', '-5 months') 是可行的，因为我的最后一篇文章是昨天发布的，但如果我的最后一篇文章是两个月前发布的，我该如何修改 WHERE 部分以返回自最后一篇文章发布日期起过去五个月的所有月份？ - Tony

@Tony，我把它改成了max(pubdate)，而不是'now'。我认为这样应该可以解决问题。 - Sam DeHaan

哎呀，我无法想出任何不同的方法，抱歉。 - Sam DeHaan

谢谢您回答原始问题，至于第二个问题，我注意到类似 WHERE ( date(pubdate) > date('2010-01-01') AND date(pubdate) < date('2010-05-01') ) 这样的语句可以工作。所以我猜我需要一些子查询之类的东西。无论如何，还是谢谢。 - Tony

Answer 2

这里是SQLite的日期和时间函数。

- xdazz

---

谢谢提到文档。它帮助我理解了几个重要的点。 - Tony