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: Use subtraction

SELECT CURRENT_DATE - 1 AS yesterday_date;

Assuming today is 2023-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 substraction. Here, since you need to subtract one day, you use - 1 to get yesterday’s date. Note that the result of this calculation still has the column type date.

Solution 2: Use INTERVAL syntax

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

Assuming today is 2023-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;