Return month name as select

Asked

Viewed 3,856 times

4

I need to return the name of the month with the command select, I know with command

Select extract('Month',data)

He returns me the number of the month in question, there is a way to bring the number of this month.

In case: 01/01/2018 would be only Janeiro

postgresql

3 answers

2


Postgresql has built-in methods for translating time strings, so that custom methods are unnecessary. You can use the function to_char, passing as first parameter the date extracted and, as second parameter, the string 'Month'.

This will return the name of the month with the first letter in upper case (there is also 'MONTH' and 'Month', if you prefer), according to the parameter lc_time defined in his postgresql.conf. In my case, where lc_time is C:

postgres=# select to_char(current_date, 'Month');
  to_char
-----------
 March
(1 row)

If you want the month name in another language without having to change the . conf, you can set the lc_time for your own session and use the string 'TMMonth'. "TM" stands for "Translation Mode":

postgres=# set lc_time  TO 'pt_BR.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 Março
(1 row)

postgres=# set lc_time  TO 'fr_FR.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 Mars
(1 row)

postgres=# set lc_time  TO 'ja_JP.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 3月
(1 row)

Thus, complementing the query initial of your question, try with:

select to_char(extract('Month',data), 'Month');

Or, if your Postgresql is not in English, use:

set lc_time to 'pt_BR.UTF-8';
select to_char(extract('Month',data), 'TMMonth');

  • that of lc_time and TMMonth did not know, very good! thanks

  • 1

    It’s really practical! = ) It also works for days of the week names (Tmday, etc.), as documented: https://www.postgresql.org/docs/10/static/functions-formatting.html

1

I use the following function:

CREATE OR REPLACE FUNCTION public.namemonth (
  integer
)
RETURNS varchar AS
$body$
SELECT Case $1
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
else NULL
end
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

To use:

Select namemonth(extract('Month',data))

0

Follow another alternative:

SELECT
   (ARRAY[
          'Janeiro',
          'Fevereiro',
          'Março',
          'Abril',
          'Maio',
          'Junho',
          'Julho',
          'Agosto',
          'Setembro',
          'Outubro',
          'Novembro',
          'Dezembro'])[ EXTRACT(MONTH FROM DATE '01.01.2018') ];

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.