Often in PostgreSQL you may want to extract the month name from a date field.
You can use the following syntax to do so:
SELECT *,
to_char(date_sold, 'Month') AS month_name
FROM products;
This particular example will extract the month name from the date in the date_sold column and save the result to a new column named month_name.
The following example shows how to use this syntax in practice.
Example: How to Extract Month Name from Date in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold at some retail store.
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', '2024-04-01');
INSERT INTO products VALUES (0002, 'A00933R', '2024-06-02');
INSERT INTO products VALUES (0003, 'BRRHHYL', '2024-07-03');
INSERT INTO products VALUES (0004, 'BR33503', '2024-09-04');
INSERT INTO products VALUES (0005, 'CHYYGG4', '2024-10-05');
INSERT INTO products VALUES (0006, 'C743559', '2024-11-06');
INSERT INTO products VALUES (0007, 'HTT56HH', '2024-12-07');
-- view all rows in table
SELECT * FROM products;
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-04-01
2 | A00933R | 2024-06-02
3 | BRRHHYL | 2024-07-03
4 | BR33503 | 2024-09-04
5 | CHYYGG4 | 2024-10-05
6 | C743559 | 2024-11-06
7 | HTT56HH | 2024-12-07
Suppose that we would like to extract the month name from each date in the date_sold column.
We can use the following syntax to do so:
SELECT *,
to_char(date_sold, 'Month') AS month_name
FROM products;
id | name | date_sold | month_name
----+---------+------------+------------
1 | A0045BH | 2024-04-01 | April
2 | A00933R | 2024-06-02 | June
3 | BRRHHYL | 2024-07-03 | July
4 | BR33503 | 2024-09-04 | September
5 | CHYYGG4 | 2024-10-05 | October
6 | C743559 | 2024-11-06 | November
7 | HTT56HH | 2024-12-07 | December
Notice that the new column named month_name contains the name of the month for the corresponding date in the date_sold column.
Note: If you would like to extract the abbreviated month name instead (e.g. “Oct”) then you can replace ‘Month’ with ‘Mon’ in the to_char() function.
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
