CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Huuuze
Huuuze

Reputation: 16357

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

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

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

Upvotes: 86

Views: 79748

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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 - 7
AND   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:

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

Upvotes: 72

Eric
Eric

Reputation: 95113

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.

Upvotes: 143

mbillard
mbillard

Reputation: 38842

What math did you try?

This should work

select * from books where current_date - integer '7'

Taken from PostgreSQL Date/Time Functions and Operators

Upvotes: 0

Related Questions