sql - 获取过去 x 周的记录

Question

我一直在努力解决有关查询 created_at 在当前或过去 x 周内的记录的问题。比方说，今天是星期三，如果 x = 1，那么从星期一午夜到现在。如果 x > 1，我正在寻找本周，直到今天或过去一周，但不使用常规interval '1 week' 因为那会让我从星期三到星期三，我只关注“整”周。

我已经尝试过间隔解决方案，也尝试过类似WHERE created_at > (CURRENT_DATE - INTERVAL '5 week')。一个适用于日、月、年等的解决方案将是首选，因为我实际上是通过其他一些后端逻辑构建查询。

我正在寻找一个通用查询“查找‘x 个时期’之前创建的所有内容。”

---

编辑:

从上次开始，我就在我的 Ruby on Rails 应用程序中实现了它。这在使用 HOUR 时造成了一些问题。内置的适用于除小时(月、日和年)以外的所有内容

SELECT "customer_uses".* 
FROM "customer_uses" 
WHERE (customer_uses.created_at > DATE_TRUNC('MONTH', TIMESTAMP '2017-09-17T16:45:01+02:00') - INTERVAL '1 MONTH')

这在我的测试用例上工作正常。 (检查计数)。 TIMESTAMP 由 DateTime.now 生成，以确保我的测试用例使用“时间旅行”测试的时间覆盖，因此不使用内置函数。

(我去掉了一些不相关的额外 WHERE 调用)。

为什么 HOUR 不起作用对我来说是个谜，因为我将它与 HOUR 而不是 MONTH 的内插字符串一起使用，如下所示:

SELECT "customer_uses".* 
FROM "customer_uses" 
WHERE (customer_uses.created_at > DATE_TRUNC('HOUR', TIMESTAMP '2017-09-17T16:45:21+02:00') - INTERVAL '1 HOUR')

Answer 1

您当前建议的查询几乎是正确的，除了它使用当前日期而不是一周的开始:

SELECT * FROM your_table WHERE created_at > (CURRENT_DATE - INTERVAL '5 week')

相反，我们可以向后检查 5 周的时间间隔，但是从本周开始:

SELECT *
FROM your_table
WHERE created_at > DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '5 week';

我相信您应该能够将上述查询用作其他时间段的模板，例如一定的月数。只需替换 DATE_TRUNC 和 WHERE 子句区间内的单位即可。