Often in PostgreSQL you may want to select rows where the month value in a date field meets a certain condition.
You can use the following basic syntax to do so:
SELECT *
FROM emp
WHERE extract(month FROM start_date) > 5;
This particular example will return all rows from the table named emp where the month in the start_date column is greater than 5, i.e. after May.
Note that we use the extract command to extract the month from each date in the start_date column as a number and then compare that number to 5.
Feel free to use >, < or = when comparing the month to a specific value.
The following example shows how to use this syntax in practice.
Example: How to Select Rows by Month 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-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-09-01', 98);
INSERT INTO emp VALUES (0005, '2024-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 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-09-01 | 98
5 | 2024-11-23 | 65
6 | 2024-02-20 | 52
7 | 2024-05-15 | 50
Suppose that we would like to select only the rows where the month in the start_date column is after May.
We can use the following syntax to do so:
SELECT *
FROM emp
WHERE extract(month FROM start_date) > 5;
empid | start_date | sales
-------+------------+-------
2 | 2024-07-01 | 201
3 | 2024-08-15 | 155
4 | 2024-09-01 | 98
5 | 2024-11-23 | 65
This returns all rows from the table where the month in the start_date column is after May.
Note that we could instead use the < operator to select all rows where the month in the start_date column is before May:
SELECT *
FROM emp
WHERE extract(month FROM start_date) < 5;
empid | start_date | sales
-------+------------+-------
1 | 2024-04-01 | 134
6 | 2024-02-20 | 52
This returns all rows from the table where the month in the start_date column is before May.
You can use similar syntax to select rows by month from your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows
