Postgres Pro
Downloads
Home   >   mailing lists

Thread: Where are the legal values for LC_TIME listed?

Where are the legal values for LC_TIME listed?

From
Bryn Llewellyn
Date:
It’s easy to guess values for, say, countries in Europe:

This:

create function to_char_demo()
  returns table(z text)
  language plpgsql
as $body$
declare
  -- Counted from midnight 1-Jan-1970 UTC.
  secs   constant double precision not null := 94996756799.456789;
  t      constant timestamp        not null := to_timestamp(-secs) at time zone 'UTC';
  fmt_1  constant text             not null := 'TMDay / TMMonth';
  fmt_2  constant text             not null := 'TMDy dd-TMMon-yyyy hh24:mi:ss.us BC';
begin
  set lc_time = 'en_US';
  z := to_char(t, fmt_1);           return next;
  z := to_char(t, fmt_2);           return next;
  z := '';                          return next;

  set lc_time = 'it_IT';
  z := to_char(t, fmt_1);           return next;
  z := to_char(t, fmt_2);           return next;
  z := '';                          return next;

  set lc_time = 'fi_FI';
  z := to_char(t, fmt_1);           return next;
  z := to_char(t, fmt_2);           return next;
end;
$body$;

select z from to_char_demo();

…brings this result:

 Monday / September
 Mon 03-Sep-1042 12:00:00.543216 BC
 
 Lunedì / Settembre
 Lun 03-Set-1042 12:00:00.543216 BC
 
 Maanantai / Syyskuu
 Ma 03-Syy-1042 12:00:00.543216 BC

But what do I use for, say, Simplified Chinese? Nothing that I guess works. And, unlike is the case with “set IntervalStyle”, a bad value doesn’t bring a hint (or a doc ref) that gives the LoV.

The obvious search (LC_TIME in the search box of the PG doc for the current version) gets no useful hits. Nor does this:


Re: Where are the legal values for LC_TIME listed?

From
Tom Lane
Date:
Bryn Llewellyn <bryn@yugabyte.com> writes:
> It’s easy to guess values for, say, countries in Europe:

On Unix-ish systems, "locale -a" should provide the set of
available values.  We don't attempt to document this
because it's so installation-dependent.

> But what do I use for, say, Simplified Chinese?

Maybe you don't have a suitable locale installed.

> The obvious search (LC_TIME in the search box of the PG doc for the current version) gets no useful hits.

The main entry for lc_time in

https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

says "Acceptable values are system-dependent; see Section 23.1 for more
information", and if you follow that link, you'll read

    What locales are available on your system under what names depends on
    what was provided by the operating system vendor and what was
    installed. On most Unix systems, the command locale -a will provide a
    list of available locales.

Not sure what more we could say.

            regards, tom lane

Re: Where are the legal values for LC_TIME listed?

From
Bryn Llewellyn
Date:
tgl@sss.pgh.pa.us wrote:

Bryn wrote:

It’s easy to guess values for, say, countries in Europe:

On Unix-ish systems, "locale -a" should provide the set of available values.  We don't attempt to document this because it's so installation-dependent.

But what do I use for, say, Simplified Chinese?

Maybe you don't have a suitable locale installed.

The obvious search (LC_TIME in the search box of the PG doc for the current version) gets no useful hits.

The main entry for lc_time in

19.11.2. Locale and Formatting: https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT 

says “Acceptable values are system-dependent; see Section 23.1 for more information", and if you follow that link, you'll read

   What locales are available on your system under what names depends on what was provided by the operating system vendor and what was
   installed. On most Unix systems, the command `locale -a` will provide a list of available locales.

Not sure what more we could say.

Thanks for the quick reply, Tom. `locale -a` showed that I do have a suitable locale installed. When I add an extra paragraph to my code that starts with `set lc_time = 'zh_CN’;`, I get this:

 星期一 / 九月
 一 03- 9-1042 12:00:00.543216 BC

What looks like an m-dash is actually the Chinese character for “one” as in “星期一” (lit. “week-one” which is the convention they adopted when they adopted the Western Calendar). Had I picked Friday (“星期五”) the output would have been nicer.

About “ Not sure what more we could say”, no… I don’t suppose there’s a cost-effective next step. In an ideal world, you’d have O/S-dependent code that reads the output of `locale -a`, sanitizes it to get the legal arguments for `set lc_time`, and presents it as a relation.

My problem is that doc search only gets me so far. Then I have to read each whole page from top to bottom to find the nuggets—in this case “ Acceptable values are system-dependent”.