SQL 你如何找到过去一周发生的结果？

How do you find results that occurred in the past week?

提问by Huuuze

I have a bookstable with a returned_datecolumn. I'd like to see the results for all of the books with a returned date that occurred in the past week.

我有一个books带returned_date列的表。我想查看返回日期发生在过去一周的所有图书的结果。

Any thoughts? I tried doing some date math, but Postgres wasn't happy with my attempt.

有什么想法吗？我尝试做一些日期计算，但 Postgres 对我的尝试并不满意。

回答by Eric

You want to use intervaland current_date:

你想使用interval和current_date：

select * from books where returned_date > current_date - interval '7 days'

This would return data from the past week includingtoday.

这将返回过去一周（包括今天）的数据。

Here's moreon working with dates in Postgres.

这里有更多关于在 Postgres 中处理日期的内容。

回答by Erwin Brandstetter

Assuming returned_dateis actually data type date, this is simpler, faster, and correct:

假设returned_date实际上是 data type date，这更简单，更快，更正确：

SELECT * FROM books WHERE returned_date > CURRENT_DATE - 7;

• now()::dateis the Postgres implementation of standard SQL CURRENT_DATE. Both do exactly the same in PostgreSQL.

• CURRENT_DATE - 7works because one can subtract / add integervalues (= days) from / to a date. An unquoted number like 7is a numeric literal defaulting to integerwhile it only contains digits and an optional leading sign, so an explicit cast is not necessary.

• With data type timestampor timestamptzyou have to add / subtract an interval, like @Eric demonstrates. You can do the same with date, but the result is timestampand you have to cast back to dateor keep working with timestamp. Sticking to datelike demonstrated is the simplest and fastest possible way. Performance difference is typically small, though.

• The computation is independent from the actual data type of returned_date, the resulting type to the right of the operator will be coerced to match either way (and raise an error if no cast is registered).

• For the "past week":

  • To include todaymake it > current_date - 7or >= current_date - 6.

  • To exclude todaymake it BETWEEN current_date - 7 AND current_date - 1(or similar). >= current_date - 7as other answers suggest returns rows for the last 8 days instead of 7and is wrong, strictly speaking.

  • To get the last full calendar week, ending with Sunday, excluding today:
    BETWEEN date_trunc('week', now())::date - 7 AND date_trunc('week', now())::date - 1

• now()::date是标准 SQL 的 Postgres 实现CURRENT_DATE。两者在 PostgreSQL 中的作用完全相同。

• CURRENT_DATE - 7之所以有效，是因为可以从 / 减去/添加integer值（= 天）到 a date。一个不带引号的数字7是一个默认的数字文字，integer而它只包含数字和一个可选的前导符号，因此不需要显式转换。

• 使用数据类型timestamp或者timestamptz您必须添加/减去一个interval，就像@Eric 演示的那样。你可以用 做同样的事情date，但结果是timestamp你必须转换回date或继续使用timestamp。坚持date喜欢示范是最简单和最快的方法。不过，性能差异通常很小。

• 计算独立于 的实际数据类型returned_date，运算符右侧的结果类型将被强制匹配任一方式（如果未注册强制转换，则会引发错误）。

• 对于“过去一周”：

  • 要包括今天使它> current_date - 7或>= current_date - 6。

  • 为了排除今日使它BETWEEN current_date - 7 AND current_date - 1（或类似）。 >= current_date - 7因为其他答案建议返回过去8 天而不是 7 天的行，严格来说是错误的。

  • 要获取以星期日结束的最后一个完整日历周，不包括今天：
    BETWEEN date_trunc('week', now())::date - 7 AND date_trunc('week', now())::date - 1

Note that the exact definition of "day" and "week" always depends on your current timezonesetting.

请注意，“天”和“周”的确切定义始终取决于您当前的timezone设置。

回答by mbillard

What math did you try?

你试过什么数学？

This should work

这应该工作

select * from books where current_date - integer '7'

Taken from PostgreSQL Date/Time Functions and Operators

摘自PostgreSQL 日期/时间函数和运算符