Often in PostgreSQL you may want to extract the year and month from a date column.
You can use the following basic syntax to do so:
SELECT to_char(start_date, 'YYYY-MM') AS year_month
FROM emp;
This particular example will extract the year and month from the date in the start_date column of the table named emp and save the results to a new column named year_month.
The following example shows how to use this syntax in practice.
Example: How to Extract Year and 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 year and month from each date in the start_date column.
We can use the following syntax to do so:
SELECT empID,
start_date,
to_char(start_date, 'YYYY-MM') AS year_month
FROM emp;
empid | start_date | year_month
-------+------------+------------
1 | 2021-04-01 | 2021-04
2 | 2022-07-01 | 2022-07
3 | 2022-08-15 | 2022-08
4 | 2022-09-01 | 2022-09
5 | 2023-11-23 | 2023-11
6 | 2024-02-20 | 2024-02
7 | 2024-05-15 | 2024-05
The new column named year_month contains only the year and month from the date in the start_date column.
Note that you could also use the to_char() statement to format the resulting year and date in a different format if you’d like.
For example, you could use the following syntax to format the output as MM/YYYY instead:
SELECT empID,
start_date,
to_char(start_date, 'MM/YYYY') AS year_month
FROM emp;
empid | start_date | year_month
-------+------------+------------
1 | 2021-04-01 | 04/2021
2 | 2022-07-01 | 07/2022
3 | 2022-08-15 | 08/2022
4 | 2022-09-01 | 09/2022
5 | 2023-11-23 | 11/2023
6 | 2024-02-20 | 02/2024
7 | 2024-05-15 | 05/2024
The new column named year_month contains the year and month from the date in the start_date column, using MM/YYYY as the format.
Feel free to use whatever format that you prefer.
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 Calculate Difference Between Rows
PostgreSQL: How to Extract Hours and Minutes from Timestamp
