CODEWITHSUNDEEP
postgresql
Jason Swett
Jason Swett

Reputation: 45074

Find records that were created X days ago

I want to get all the paid_account records that were created exactly 11 days ago. Here's what I have now:

SELECT *
  FROM paid_account
 WHERE date_trunc('day', created_at) = date_trunc('day', date ?)

-- The ? gets replaced with today's date in the user's timezone.

This works but it seems like there must be a simpler way to do it. Is there a better way?

Upvotes: 3

Views: 1939

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

In standard SQL, you'd subtract a value of type interval from either a value of type date or a value of type timestamp. PostgreSQL supports standard SQL in this case.

Since columns named "created_at" are usually timestamps, you can do something like this.

select *
from paid_account
where cast(created_at as date) = current_date - interval '11' day;

But the WHERE clause isn't sargable. (PostgreSQL-specific syntax, as created_at::date isn't sargable, either.) If you look at the execution plan, you'll find that it invariably uses a sequential scan.

Creating an index on "created_at" alone won't help. You need an index on the expression date(created_at) to get good performance.

create index on paid_account (date(created_at));

Upvotes: 1

Tomas Greif
Tomas Greif

Reputation: 22623

You can substract 11 from current_date and compare it with created_at casted to date:

created_at::date = (current_date - 11)

Upvotes: 3

Related Questions