In PostgreSQL, the CURRENT_DATE is a built-in function that provides the current/prevailing date. It doesn’t take any parameters. The CURRENT_DATE -1 is used to fetch the day before the current date. In Postgres, we must subtract “1” from the current date to fetch the day before today i.e., ‘CURRENT_DATE - 1’.
So, let’s learn how to find the day before the current date in PostgreSQL with the help of examples. In this write-up, you will learn the below-listed learning outcomes regarding the current date minus 1 day:
- How to Fetch the Current Date in Postgres?
- How to Get/Fetch the Current Date Minus 1 Day in Postgres?
- How to Insert Current Date Minus 1 Day to a Postgres Table?
So, let’s get started!
How to Fetch the Current Date in Postgres?
The CURRENT_DATE function is used to fetch the system’s current date and doesn’t require any parameter, as shown in the following syntax:
CURRENT_DATE;
The CURRENT_DATE function returns the date in YYYY-MM-DD format.
Let’s open the SQL SHELL, fill in the required information, provide the super user password and run the below statement to get the system’s current date:
SELECT CURRENT_DATE;
The output shows that the CURRENT_DATE returns the accurate date.
How to Get/Fetch the Current Date Minus 1 Day in Postgres?
Up till now, you have learned how to fetch the current date. However, this write-up focuses on finding the day before the current date. To do so, you must subtract 1 from the current date as shown below:
CURRENT_DATE - 1;
Let’s implement this concept practically into the SQL SHELL:
SELECT CURRENT_DATE - 1;
This command will subtract the 1 day from the system’s current day. In our system, the current date is “2022-08-13”. So, the “CURRENT_DATE - 1” will return “2022-08-12”:
By comparing this output with the previous one, you will come to know that the CURRENT_DATE - 1 provides an accurate result (i.e. current date - 1).
How to Insert Current Date Minus 1 Day to a Postgres Table?
We can utilize the “CURRENT_DATE - 1” to insert a day before today into a specific table. Suppose we already have a table named article_details whose details are as follows:
SELECT * FROM article_details;
Let’s insert two more rows into the article_details table. We will pass the system’s current date as the published date in the first row. While in the second row, we will pass the current date minus 1 as the published date:
INSERT INTO article_details(article_id, article_title, published_date)
VALUES ('12', 'PostgreSQL BETWEEN Operator', CURRENT_DATE),('11', 'PostgreSQL FETCH Clause', CURRENT_DATE - 1);
The output shows that two rows have been inserted into the article_details table successfully. Let’s see how the updated table looks:
The output verified that the system’s current date and ‘current date -1’ had been successfully inserted into the targeted table.
Conclusion
To get the current date minus 1 day, we have to subtract “1” from the current date. In PostgreSQL, the CURRENT_DATE is an inbuilt function that is used to fetch the prevailing date. It doesn’t take any parameters. It returns a date value in YYYY-MM-DD format, and the returned date will be according to the system’s current date. This post explained the basics of the Postgres CURRENT_DATE function with the help of examples.
