SQL PostgreSQL 查询以选择上周的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30783452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Query to select data from last week?
提问by Gabriel Lidenor
I have a table which has all the purchases of my costumers. I want to select all entries from the last week, (week start from Sunday).
我有一张桌子，上面有我的顾客购买的所有商品。我想选择上周的所有条目（从星期日开始的一周）。
id value date
5907 1.20 "2015-06-05 09:08:34-03"
5908 120.00 "2015-06-09 07:58:12-03"
I've tried this:
我试过这个：
SELECT id, valor, created, FROM compras WHERE created >= now() - interval '1 week' and parceiro_id= '1'
But I got the data from the last week including data from this week, I only want data from the last week.
但是我得到了上周的数据，包括本周的数据，我只想要上周的数据。
How to get data only from last week ?
如何只获取上周的数据？
回答by Nicolai
This condition will return records from Sunday till Saturday last week:
此条件将返回上周从周日到周六的记录：
WHERE created BETWEEN
NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7
AND NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER
There is an example:
有一个例子：
WITH compras AS (
SELECT ( NOW() + (s::TEXT || ' day')::INTERVAL )::TIMESTAMP(0) AS created
FROM generate_series(-20, 20, 1) AS s
)
SELECT to_char( created, 'DY'::TEXT), created
FROM compras
WHERE created BETWEEN
NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7
AND NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER
In answer to @d456:
回复@d456：
Wouldn't using
BETWEENinclude midnight on Sunday at both ends of the interval?
不会
BETWEEN在间隔的两端使用包括周日午夜吗？
That right, BETWEENincludes midnight on Sunday at both ends of the interval. To exclude midnight on Sunday at end of interval it is necessary to use operators >=and <:
那个权利，BETWEEN包括间隔两端的周日午夜。要在间隔结束时排除周日午夜，必须使用运算符>=和<：
WITH compras AS (
SELECT s as created
FROM generate_series( -- this would produce timestamps with 20 minutes step
(now() - '20 days'::interval)::date,
(now() + '20 days'::interval)::date,
'20 minutes'::interval) AS s
)
SELECT to_char( created, 'DY'::TEXT), created
FROM compras
WHERE TRUE
AND created >= NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-7
AND created < NOW()::DATE-EXTRACT(DOW from NOW())::INTEGER
回答by Gordon Linoff
Postgres by default starts weeks on a Sunday, so you are in luck. You can use date_trunc()to get the beginning of the previous week:
Postgres 默认在星期日开始几周，所以你很幸运。您可以使用date_trunc()获取前一周的开始时间：
WHERE (created >= date_trunc('week', CURRENT_TIMESTAMP - interval '1 week') and
created < date_trunc('week', CURRENT_TIMESTAMP)
)
EDIT:
编辑：
Postgres by default starts week for date_truncon Monday, but for dow on Sunday. So, you can do what you want by using that logic, which Nicolai has in his answer.
默认情况下，Postgres在星期一开始date_trunc一周，但在星期日开始dow。因此，您可以使用 Nicolai 在他的回答中给出的逻辑来做您想做的事。
