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;

Answer 2

You can set function-scoped config parameters (including in IMMUTABLE or STABLE functions) with set_config(setting_name, new_value, is_local).

So, if you want to format your date in fr_FR you can do something like:

CREATE OR REPLACE FUNCTION format_month() RETURNS TEXT AS $$
  SELECT set_config('lc_time', 'fr_FR.utf8', true);
  SELECT to_char(now(), 'TMMonth');
$$ LANGUAGE sql STABLE;

(Note the TM- prefix enables translation mode, which prints localized day and month names based on lc_time.)

On my computer, the above function produced an error until I installed the fr_FR locale and restarted postgres. How you do that depends on your OS, but on Ubuntu I used dpkg-reconfigure locales.