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
andTMMonth
did not know, very good! thanks– Rovann Linhalis
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
– nunks