0

We just switched from MySQL to Postgres 13.6.

I am trying to return results for unpaid invoices that are between 30 and 59 days from invoice create date, so anything that has gone 30 and 59 days unpaid since being created.

In MySQL this was done as:

and datediff(current_date,date(`invoices`.`created_at`)) BETWEEN  30 AND 59;

I've tried a few different things with intervals and BETWEEN, but nothing seems to work.

gothxbrooks
  • 1
  • 1
  • 1
    If you drop the time part by casting to date, a subtraction yields integer days: `and current_date - invoices.created_at::date between 30 and 59;` – Mike Organek Jun 16 '22 at 22:52
  • @MikeOrganek thank you so much!! I'm getting the result I got last time it worked, but maybe that's just our sad reality currently. If I wanted to do it for a time period greater than 60 days would it just be the ```::date >= 60;```? – gothxbrooks Jun 16 '22 at 23:12
  • Yes. Can you edit your question to describe the problem you have with the result? Also, `>= 60` should work. – Mike Organek Jun 16 '22 at 23:31
  • Please show the exact definition of `invoices.created_at`. – Erwin Brandstetter Jun 17 '22 at 00:02

1 Answers1

0

Be sure to use a sargable expression like:

WHERE invoices.created_at BETWEEN current_date - 59
                              AND current_date - 30

Postgres can add/subtract integer to/from date. Example:

The definition of lower and upper bound are fuzzy. Not sure how you want to round upper and lower bound, and not sure you are aware that current_date depends on the timezone setting of your current session. I am also not confident your MySQL expression did exactly what you wanted around the edges ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228