How to Get Yesterday’s Date in PostgreSQL

Problem:

You would like to display yesterday's date (without time) in a PostgreSQL database.

Solution 1:

SELECT current_date - INTEGER '1' AS yesterday_date;

Assuming today is 2020-09-24, the result is:

Discussion:

To get yesterday's date, you need to subtract one day from today's date. Use current_date to get today's date. Note that you don’t need brackets at the end of the current_date function. In PostgreSQL, you can subtract or add any number of days using the INTEGER keyword. Here, since you need to subtract one day, you use - INTEGER '1' to get yesterday’s date. Note that the result of this calculation still has the column type date.

Solution 2:

SELECT (current_date - INTERVAL '1 day')::date AS yesterday_date;

Assuming today is 2020-09-24, the result is:

Discussion:

Get today's date using current_date. You need to subtract an interval of one day from the current date. To do this, use the INTERVAL keyword, which creates any time/date interval you want (here, '1 day', which in PostgreSQL is the same as 24 hours). Subtracting INTERVAL '1 day' from today's date will result in a column formatted as a timestamp, so you need to cast it to date. The quickest way to do so in PostgreSQL is appending ::date to the whole expression (remember to enclose the expression in parentheses).

Of course, you can go back by any time interval just as easily. Here's an example:

SELECT (current_date - INTERVAL '3 months 10 days')::date;

An INTERVAL can also be added to a date. So, here’s a way if you want to get tomorrow's date:

SELECT (current_date + INTERVAL '1 day')::date AS tomorrow_date;