PostgreSQL facilitates us with numerous Date and Time functions, such as NOW(), CURRENT_TIME, CURRENT_TIMESTAMP, etc. These functions assist us in storing or retrieving the date and time values smoothly. Postgres offers various functions that retrieve today’s date time. However, there is no such function that directly retrieves yesterday’s date.
This article will illustrate a complete guide on getting yesterday’s date.
How to Get/Check Yesterday’s Date in Postgres?
Any Postgres function that retrieves today’s date can be used with the “-” operator to get yesterday’s date. Here are some frequently used functions that retrieve today’s date:
- CURRENT_DATE
- NOW()
- CURRENT_TIMESTAMP
Let’s consider the below examples to learn how to use any of the above-provided functions to fetch yesterday’s date.
Example 1: Getting Yesterday’s Date Using CURRENT_DATE Function
In the following example, the CURRENT_DATE function will be utilized to get the day before today’s date:
SELECT CURRENT_DATE AS today, CURRENT_DATE - INTEGER '1' AS yesterday;
In the above snippet, we utilized the “CURENT_DATE” function to get today’s and yesterday’s dates side-by-side:
The output shows that the CURRENT_DATE function successfully retrieves yesterday's date.
Example 2: Getting Yesterday’s Date Using NOW() Function
Alternatively, the NOW() function can be used with the “-” operator to achieve the same functionality. However, the NOW() function retrieves a timestamp value, therefore, it must be cased to the DATE data type:
SELECT NOW() AS today, NOW():: DATE - INTEGER '1' AS yesterday;
Similarly, the CURRENT_TIMESTAMP function can also be used to get yesterday’s date:
SELECT CURRENT_TIMESTAMP AS today, CURRENT_TIMESTAMP:: DATE - INTEGER '1' AS yesterday;
The output demonstrates that the CURRENT_TIMESTAMP() function successfully retrieves yesterday's date.
Example 3: Getting Yesterday’s Date Using INTERVAL
Yesterday’s data can also be fetched by using the CURRENT_DATE function with an INTERVAL. Here is an example code:
SELECT CURRENT_DATE AS today, (CURRENT_DATE - INTERVAL'1 day') :: DATE AS yesterday;
The output proves that yesterday’s date has been successfully retrieved.
Conclusion
Postgres offers various functions that retrieve today’s date time. However, there is no such function that directly retrieves yesterday’s date. Any Postgres function that retrieves today’s date can be used with the “-” operator to get yesterday’s date. For instance, the “SELECT CURRENT_DATE - INTEGER '1' AS yesterday;” retrieves yesterday’s date in Postgres. This article has explained various methods to get yesterday’s date in PostgreSQL.
