Change PostgreSQL date language from request

Question

I'm kind of new to PostgreSQL and I'm trying to change the locale of the date function results, to get the result of to_char(my_date, 'Month') in another language.

Here are some of my settings :

$> show lc_time;

en_US.UTF-8

I found in the documentation that the locale could be changed at the database initialization :

initdb --locale=fr_FR

But that's not exactly what I'm looking for.

In MySQL, I used to do it like :

SET lc_time_names = 'fr_FR';

But unfortunately, I can't find a way to do it with PostgreSQL. Is there any ?

Answer 1

You can get the localized version of the name of the month with the special TM format (translation mode):

to_char(my_date, 'TMMonth')

You can also apply this to the names of the weekdays with TMD. Both versions will display the names using the locale of the server, unless the lc_time run-time parameter has a specific setting. That setting can also be changed for the current session:

SET lc_time = 'fr_FR';

When you want to revert back to the default setting in the same session, use:

SET lc_time TO DEFAULT;