PostgreSQL Query to select data from last week?

Question

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 ?

Answer 1

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:

Wouldn't using BETWEEN include midnight on Sunday at both ends of the interval?

That right, BETWEEN includes 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 <:

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

Answer 2

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:

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_trunc on Monday, but for dow on Sunday. So, you can do what you want by using that logic, which Nicolai has in his answer.