Often in PostgreSQL you may want to select all records from a table where the date is equal to yesterday.
You can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_date = CURRENT_DATE - interval '1 day';
This particular example will select all records from the table named emp where the date in the start_date column is equal to yesterday’s date.
Note that the CURRENT_DATE command in PostgreSQL returns the current date.
Using this query, we are able to subtract 1 day from the current date and filter the table to only show records where the date in the start_date column is equal to yesterday.
The following example shows how to use this syntax in practice.
Example: Select All Records from Yesterday in PostgreSQL
Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:
-- create table named 'emp'
CREATE TABLE emp (
empID INT PRIMARY KEY,
start_date DATE,
sales INT
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-08-01', 134);
INSERT INTO emp VALUES (0002, '2024-07-01', 201);
INSERT INTO emp VALUES (0003, '2024-08-22', 155);
INSERT INTO emp VALUES (0004, '2024-08-27', 98);
INSERT INTO emp VALUES (0005, '2024-08-28', 65);
INSERT INTO emp VALUES (0006, '2024-06-20', 52);
INSERT INTO emp VALUES (0007, '2024-08-27', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2024-08-01 | 134
2 | 2024-07-01 | 201
3 | 2024-08-22 | 155
4 | 2024-08-27 | 98
5 | 2024-08-28 | 65
6 | 2024-06-20 | 52
7 | 2024-08-27 | 50
This article is currently being written on 8/28/2024.
Suppose that we would like to select all records from this table where the date in the start_date column is from yesterday (8/27/2024).
We can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_date = CURRENT_DATE - interval '1 day';
empid | start_date | sales
-------+------------+-------
4 | 2024-08-27 | 98
7 | 2024-08-27 | 50
The resulting table contains all records from the original table where the date in the start_date column is equal to yesterday’s date.
Note: If you would like to select all records from yesterday and today, then simply change the = sign in the WHERE statement to a >= sign.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: Select All Records from Last 7 Days
PostgreSQL: How to Extract Date from a Timestamp
