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

You want to use interval and current_date:

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

This would return data from the past week including today.

Here's more on working with dates in Postgres.

Assuming returned_date is data type date, this is simplest and fastest:

SELECT * FROM books WHERE returned_date > CURRENT_DATE - 7;

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

CURRENT_DATE - 7 works because one can subtract / add integer values (= days) from / to a date. An unquoted number like 7 is treated as numeric constant and initially cast to integer by default (only digits, plus optional leading sign). No explicit cast needed.

With data type timestamp or timestamptz you have to add / subtract an interval, like @Eric demonstrates. You can do the same with date, but the result is timestamp and you have to cast back to date or keep working with timestamp. Sticking to date is simplest and fastest for your purpose. Performance difference is tiny, but there is no reason not to take it. Less error prone, too.

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 (or raise an error if no cast is registered).

For the "past week" ...

To include today make it > current_date - 7 or >= current_date - 6. But that's typically a bad idea, as "today" is only a fraction of a day and can produce odd results.
>= current_date - 7 returns rows for the last 8 days (incl. today) instead of 7 and is wrong, strictly speaking.

To exclude today make it:

WHERE returned_date >= current_date - 7AND   returned_date <  current_date

Or:

WHERE returned_date BETWEEN current_date - 7                    AND     current_date - 1

To get the last full calendar week ending with Sunday, excluding today:

WHERE returned_date BETWEEN date_trunc('week', now())::date - 7                    AND     date_trunc('week', now())::date - 1

BETWEEN ... AND ... is ok for data type date (being a discrete type), but typically the wrong tool for timestamp / timestamptz. See:

• How to add a day/night indicator to a timestamp column?

The exact definition of "day" and "week" always depends on your current timezone setting.

What math did you try?

This should work

select * from books where current_date - integer '7'

Taken from PostgreSQL Date/Time Functions and Operators