Often in PostgreSQL you may want to select all rows from the previous month.
You can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_date >= date_trunc('month', current_date - interval '1 month')
AND start_date < date_trunc('month', current_date);
This particular example selects all rows from the emp table where the date in the start_date column is from the previous month.
Note that we use a WHERE clause to filter for rows where the date in the start_date column is greater than or equal to the first day of the previous month AND less than the first day of the current month.
Note: The current_date function returns the current date in PostgreSQL.
The following example shows how to use this syntax in practice.
Example: How to Select All Rows from Previous Month in PostgreSQL
Suppose that we create the following table named emp that contains information about when various employees started working at some company:
-- 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-04-01', 134);
INSERT INTO emp VALUES (0002, '2024-07-01', 201);
INSERT INTO emp VALUES (0003, '2024-08-15', 155);
INSERT INTO emp VALUES (0004, '2024-08-17', 98);
INSERT INTO emp VALUES (0005, '2024-08-23', 65);
INSERT INTO emp VALUES (0006, '2024-09-20', 52);
INSERT INTO emp VALUES (0007, '2024-09-24', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2024-04-01 | 134
2 | 2024-07-01 | 201
3 | 2024-08-15 | 155
4 | 2024-08-17 | 98
5 | 2024-08-23 | 65
6 | 2024-09-20 | 52
7 | 2024-09-24 | 50
This article is currently being written on September 25th, 2024.
Suppose that we would like to select all rows from the table where the date in the start_date column is from the previous month, i.e. from August of 2024.
We can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_date >= date_trunc('month', current_date - interval '1 month')
AND start_date < date_trunc('month', current_date);
empid | start_date | sales
-------+------------+-------
3 | 2024-08-15 | 155
4 | 2024-08-17 | 98
5 | 2024-08-23 | 65
Notice that the resulting table only contains the rows where the date in the start_date column is from the previous month, i.e. from August of 2024.
You can use similar syntax to select all rows from the previous month in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select All Records from Yesterday
PostgreSQL: How to Select All Records from Last 7 Days
PostgreSQL: How to Add Months to a Date
