PostgreSQL: How to Extract Month from Date

Often in PostgreSQL you may want to extract a month from a date column.

You can use the following basic syntax to do so:

SELECT extract(month FROM start_date) AS month
FROM emp;

This particular example will extract the month from the date in the start_date column of the table named emp and save the results to a new column named month.

The following example shows how to use this syntax in practice.

Example: How to Extract Month from Date 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, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-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 | 2021-04-01 |   134
     2 | 2022-07-01 |   201
     3 | 2022-08-15 |   155
     4 | 2022-09-01 |    98
     5 | 2023-11-23 |    65
     6 | 2024-02-20 |    52
     7 | 2024-05-15 |    50

Suppose that we would like to extract the month from each date in the start_date column.

We can use the following syntax to do so:

SELECT empID,
       start_date,
       extract(month FROM start_date) AS month
FROM emp;

 empid | start_date | month 
-------+------------+-------
     1 | 2021-04-01 |     4
     2 | 2022-07-01 |     7
     3 | 2022-08-15 |     8
     4 | 2022-09-01 |     9
     5 | 2023-11-23 |    11
     6 | 2024-02-20 |     2
     7 | 2024-05-15 |     5

The new column named month contains only the month from the date in the start_date column.

For example, we can see:

• Employee 1 started in month 4.
• Employee 2 started in month 7.
• Employee 3 started in month 8.

And so on.

You can use similar syntax to extract the month from a date column in 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