Where are the legal values for LC_TIME listed?
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:
https://www.google.com/search?client=safari&rls=en&q=LC_TIME+site:https://www.postgresql.org/docs/13/&ie=UTF-8&oe=UTF-8 <https://www.google.com/search?client=safari&rls=en&q=LC_TIME+site:https://www.postgresql.org/docs/13/&ie=UTF-8&oe=UTF-8>
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
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”.