PostgreSQL: How to Get Month Name from Number

Often in PostgreSQL you may want to get a month name from a number.

You can use the following syntax to do so:

SELECT *,
       to_char(to_timestamp(month_sold::text, 'MM'), 'Month') AS month_name
FROM products;

This particular example will return the month name from the number in the month_sold column and save the result to a new column named month_name.

Note that this will return the full month name, e.g. January.

If you would like to return the abbreviated month name, e.g. Jan, then you can replace ‘Month’ with ‘Mon’ in the second argument of the to_timestamp() function.

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

Example: How to Get Month Name from Number in PostgreSQL

Suppose that we create the following table named products that contains information about the month when various products were sold at some retail store:

-- create table named 'products'
CREATE TABLE products (
  name TEXT,
  month_sold INTEGER
);

-- add rows to the table 
INSERT INTO products VALUES ('A', 1);
INSERT INTO products VALUES ('B', 4);
INSERT INTO products VALUES ('C', 7);
INSERT INTO products VALUES ('D', 8);
INSERT INTO products VALUES ('E', 10);
INSERT INTO products VALUES ('F', 12);

-- view all rows in table
SELECT * FROM products;

 name | month_sold 
------+------------
 A    |          1
 B    |          4
 C    |          7
 D    |          8
 E    |         10
 F    |         12

Suppose that we would like to get the month name for each month number in the month_sold column.

We can use the following syntax to do so:

SELECT *,
       to_char(to_timestamp(month_sold::text, 'MM'), 'Month') AS month_name
FROM products;

 name | month_sold | month_name 
------+------------+------------
 A    |          1 | January  
 B    |          4 | April    
 C    |          7 | July     
 D    |          8 | August   
 E    |         10 | October  
 F    |         12 | December

Notice that the new column named month_name contains the name of the month for the corresponding month number in the month_sold column.

Note that you could also use the following syntax to return the abbreviated month names instead:

SELECT *,
       to_char(to_timestamp(month_sold::text, 'MM'), 'Mon') AS month_name
FROM products;

 name | month_sold | month_name 
------+------------+------------
 A    |          1 | Jan
 B    |          4 | Apr
 C    |          7 | Jul
 D    |          8 | Aug
 E    |         10 | Oct
 F    |         12 | Dec

The new column named month_name now contains the abbreviated name of the month for the corresponding month number in the month_sold column.

Feel free to use whichever method 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 Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows