CODEWITHSUNDEEP
sqlpostgresql
uzhas
uzhas

Reputation: 925

List of months and values from sql postgressql

How can write query to get all months and values. For example :

Value    Month
  1       January
  2       February

and so on...

Upvotes: 0

Views: 89

Answers (2)

forpas
forpas

Reputation: 164099

Use generate_series() to get the month numbers and then with TO_DATE() and TO_CHAR() convert the numbers to month names:

SELECT Value, 
       TO_CHAR(TO_DATE(Value::text, 'MM'), 'Month') AS "Month"
FROM generate_series(1, 12) Value;

See the demo.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

Using generate_series:

SELECT ROW_NUMBER() OVER(ORDER BY s) AS value,
       TO_CHAR(s, 'Month') AS Month
FROM generate_series('2020-01-01'::date, '2020-12-31'::date, '1 month'::interval) s

SELECT s.value, 
       TO_CHAR('2020-01-01'::date + (s.value - 1) * '1 month'::interval, 'Month') AS month
FROM generate_series(1,12) s(value);

db<>fiddle demo

Upvotes: 1

Related Questions