Thread: lc_time and localized dates
Hi,
Attached is a patch that replaces the lc_messages with lc_time when
using to_char in translation mode (TM) [1]. It doesn't change the output
behaviour. Per discussion [2], it's using some cache mechanism so we
don't need to call setlocale() all the time.
Some issues:
(i) some locales don't capitalize the first letter. I'm using
pg_toupper() to do the job but I'm afraid it's not appropriated. I'm
using it 'cause i'm too lazy to move localized_str_toupper(). Any
suggestions?
(ii) it didn't address the problem spotted at [3][4]. IMHO, it's ok for
a non-superuser to set lc_time. Opinions?
euler=# show lc_time;
lc_time
---------
pt_BR
(1 registro)
euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth YYYY');
to_char
--------------------------------------------------------
Segunda Monday mon, 25 Fev February fevereiro 2008
(1 registro)
euler=# set lc_time to 'es_ES';
SET
euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth YYYY');
to_char
----------------------------------------------------
Lunes Monday mon, 25 Feb February febrero 2008
(1 registro)
euler=# set lc_time to 'de_DE';
SET
euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth YYYY');
to_char
-----------------------------------------------------
Montag Monday mon, 25 Feb February februar 2008
(1 registro)
euler=# set lc_time to 'fr_FR';
SET
euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth YYYY');
to_char
----------------------------------------------------
Lundi Monday mon, 25 Fév February février 2008
(1 registro)
euler=# set lc_time to 'C';
SET
euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth YYYY');
to_char
------------------------------------------------------
Monday Monday mon, 25 Feb February february 2008
(1 registro)
[1] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00539.php
[2] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00693.php
[3] http://archives.postgresql.org/pgsql-hackers/2007-10/msg00214.php
[4] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00692.php
--
Euler Taveira de Oliveira
http://www.timbira.com/
Attachment
Euler Taveira de Oliveira napsal(a):
> Hi,
Hi
> Some issues:
> (i) some locales don't capitalize the first letter. I'm using
> pg_toupper() to do the job but I'm afraid it's not appropriated. I'm
> using it 'cause i'm too lazy to move localized_str_toupper(). Any
> suggestions?
Please, Day names does not have capitalized first letter in Czech language. We
have "pondeli" as a Monday. If locale does not do that it is probably intention :-).
Zdenek
Zdenek Kotala wrote:
> Please, Day names does not have capitalized first letter in Czech
> language. We have "pondeli" as a Monday. If locale does not do that it
> is probably intention :-).
>
Hmmm... I don't know about that. I do it that way 'cause I'm concerned
about some locales that don't capitalize (see above). In my head,
(i) 'TMMonth' is February, Fevereiro, Febrero, Únor;
(ii) 'TMmonth' is february, fevereiro, febrero, únor;
(iii) 'TMMONTH' is FEBRUARY, FEVEREIRO, FEBRERO, ÚNOR.
How would we handle that case? Is it wrong to write "Únor"?
euler=# set lc_time to 'cs_CZ';
SET
euler=# select to_char(now(), 'TMDay TMDy TMMon TMMonth');
to_char
---------------------
Pondìlí Po úno únor
(1 registro)
euler=# set lc_time to 'es_ES';
SET
euler=# select to_char(now(), 'TMDay TMDy TMMon TMMonth');
to_char
-----------------------
lunes lun feb febrero
(1 registro)
euler=# set lc_time to 'fr_FR';
SET
euler=# select to_char(now(), 'TMDay TMDy TMMon TMMonth');
to_char
-----------------------
lundi lun fév février
(1 registro)
euler=# set lc_time to 'it_IT';
SET
euler=# select to_char(now(), 'TMDay TMDy TMMon TMMonth');
to_char
-------------------------
lunedì lun feb febbraio
(1 registro)
euler=# set lc_time to 'hu_HU';
SET
euler=# select to_char(now(), 'TMDay TMDy TMMon TMMonth');
to_char
----------------------
hétfõ h febr február
(1 registro)
--
Euler Taveira de Oliveira
http://www.timbira.com/
Euler Taveira de Oliveira napsal(a):
> Zdenek Kotala wrote:
>
>> Please, Day names does not have capitalized first letter in Czech
>> language. We have "pondeli" as a Monday. If locale does not do that it
>> is probably intention :-).
>>
> Hmmm... I don't know about that. I do it that way 'cause I'm concerned
> about some locales that don't capitalize (see above). In my head,
> (i) 'TMMonth' is February, Fevereiro, Febrero, Únor;
> (ii) 'TMmonth' is february, fevereiro, febrero, únor;
> (iii) 'TMMONTH' is FEBRUARY, FEVEREIRO, FEBRERO, ÚNOR.
>
> How would we handle that case? Is it wrong to write "Únor"?
Yes it is. Only if it is a first word in a sentence or name you should
use "Ú". Also name of day is 'pondělí' (Monday) with small p
If I read description of "Month" pattern, it says "full mixed-case month
name (blank-padded to 9 chars)". By my opinion this patter should keep
standard locale behavior - it means for English February, for Czech únor.
Zdenek
> Attached is a patch that replaces the lc_messages with > lc_time when using to_char in translation mode (TM) [1]. It > doesn't change the output behaviour. Per discussion [2], it's > using some cache mechanism so we don't need to call > setlocale() all the time. Have you tested this patch on MSVC and MinGW (Windows) builds? changing LC_MESSAGES/LC_TIME will most probably break the Windows behavior. > Some issues: > (i) some locales don't capitalize the first letter. I'm using > pg_toupper() to do the job but I'm afraid it's not > appropriated. AFAIK, some locales like the Dutch doesn't have capitalized first letters for month/day. Making the first letter capitalized for all values would be incorrect. Regards, Gevik
Magnus,
Please look at this patch before you check my last patch about the locale.
It seems that Euler's solution also fixes the windows locale bug that I was
trying to fix.
Replacing the lc_messages with lc_time when using to_char seems to be the
correct direction.
I have compiled and tested Euler's patch on MSVC and got the correct
results.
Euler,
I think your patch is correct for the most part except you should not force
the first letter of day/month names
to uppercase hence this is not grammatically correct for some languages.
After a quick glimpse of your cache mechanism, I think we can use this to
solve some other TO_CHAR/TO_DATE items.
Regards,
Gevik.
Resuts:
Gevik=# set lc_time to 'Dutch, Netherlands';
SET
Gevik=# select to_char(now() + '8 months'::interval, 'TMDay Day dy, DD TMMon
Month TMmonth YYYY');
to_char
-----------------------------------------------------
Zondag Sunday sun, 26 Okt October oktober 2008
(1 row)
Gevik=#
> -----Original Message-----
> From: pgsql-patches-owner@postgresql.org
> [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of
> Euler Taveira de Oliveira
> Sent: Monday, February 25, 2008 5:53 AM
> To: PostgreSQL-patches
> Subject: [PATCHES] lc_time and localized dates
>
> Hi,
>
> Attached is a patch that replaces the lc_messages with
> lc_time when using to_char in translation mode (TM) [1]. It
> doesn't change the output behaviour. Per discussion [2], it's
> using some cache mechanism so we don't need to call
> setlocale() all the time.
>
> Some issues:
> (i) some locales don't capitalize the first letter. I'm using
> pg_toupper() to do the job but I'm afraid it's not
> appropriated. I'm using it 'cause i'm too lazy to move
> localized_str_toupper(). Any suggestions?
> (ii) it didn't address the problem spotted at [3][4]. IMHO,
> it's ok for a non-superuser to set lc_time. Opinions?
>
> euler=# show lc_time;
> lc_time
> ---------
> pt_BR
> (1 registro)
>
> euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month
> TMmonth YYYY');
> to_char
> --------------------------------------------------------
> Segunda Monday mon, 25 Fev February fevereiro 2008
> (1 registro)
>
> euler=# set lc_time to 'es_ES';
> SET
> euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month
> TMmonth YYYY');
> to_char
> ----------------------------------------------------
> Lunes Monday mon, 25 Feb February febrero 2008
> (1 registro)
>
> euler=# set lc_time to 'de_DE';
> SET
> euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month
> TMmonth YYYY');
> to_char
> -----------------------------------------------------
> Montag Monday mon, 25 Feb February februar 2008
> (1 registro)
>
> euler=# set lc_time to 'fr_FR';
> SET
> euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month
> TMmonth YYYY');
> to_char
> ----------------------------------------------------
> Lundi Monday mon, 25 Fév February février 2008
> (1 registro)
>
> euler=# set lc_time to 'C';
> SET
> euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month
> TMmonth YYYY');
> to_char
> ------------------------------------------------------
> Monday Monday mon, 25 Feb February february 2008
> (1 registro)
>
>
>
>
> [1] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00539.php
> [2] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00693.php
> [3] http://archives.postgresql.org/pgsql-hackers/2007-10/msg00214.php
> [4] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00692.php
>
>
> --
> Euler Taveira de Oliveira
> http://www.timbira.com/
>
Attachment
Gevik Babakhani wrote: > Have you tested this patch on MSVC and MinGW (Windows) builds? > changing LC_MESSAGES/LC_TIME will most probably break the Windows behavior. > No. [Looking at the code...] I think it only affects the LC_MESSAGES 'cause setlocale(LC_MESSAGES) don't work on Windows. > AFAIK, some locales like the Dutch doesn't have capitalized first letters > for month/day. > Making the first letter capitalized for all values would be incorrect. > Even if we have just the month word? Don't consider a sentence in this case. We're talking about a formatting function so you can choose if you want it capitalized or not. Is it ok to output TMDay as 'Dinsdag' ? -- Euler Taveira de Oliveira http://www.timbira.com/
Zdenek Kotala wrote: > Yes it is. Only if it is a first word in a sentence or name you should > use "Ú". Also name of day is 'pondělí' (Monday) with small p > But we're not talking about *sentence*, we need to consider just the *word*. So I think TMMonth should be Xxxxxxxx, TMMONTH should be XXXXXXXX, and TMmonth should be xxxxxxxx. If you want to ensure lowercase in months, you could use TMmonth instead of TMMonth. -- Euler Taveira de Oliveira http://www.timbira.com/
Euler Taveira de Oliveira napsal(a):
> Zdenek Kotala wrote:
>
>> Yes it is. Only if it is a first word in a sentence or name you should
>> use "Ú". Also name of day is 'pondělí' (Monday) with small p
>>
> But we're not talking about *sentence*, we need to consider just the
> *word*. So I think TMMonth should be Xxxxxxxx, TMMONTH should be
> XXXXXXXX, and TMmonth should be xxxxxxxx.
> If you want to ensure lowercase in months, you could use TMmonth instead
> of TMMonth.
But how you handle situation when you have multi language application
which needs correct output for all languages? You cannot use any of the
pattern because it will be wrong for some group of languages.
Zdenek
> No. [Looking at the code...] I think it only affects the > LC_MESSAGES 'cause setlocale(LC_MESSAGES) don't work on Windows. In order to make setlocale(LC_MESSAGES) affect on windows some additional steps must be taken. I don't go deep in that now. I have a fix with description etc. etc. > Is it ok to output TMDay as 'Dinsdag' ? No. you don't write dinsdag with a D in Dutch. Looking into code for a couple of days now and the recent discussions, I must say that there is more broken in locale to_char/to_date (Windows) than we can see at first glance. I am going to report my finding a.s.a.p. Then we can discuss about a fix. Regards, Gevik
Am Dienstag, 26. Februar 2008 schrieb Zdenek Kotala: > But how you handle situation when you have multi language application > which needs correct output for all languages? You cannot use any of the > pattern because it will be wrong for some group of languages. This is what you get when you copy a proprietary, poorly specified interface. The to_char functions are supposed to be Oracle-compatible, so we need to check what Oracle does. Whether that is useful in practice is a bit secondary. I'm beginning to think, if you want formatting functions that are more sane, stable, and standardized, export sprintf and strftime to PostgreSQL. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Zdenek Kotala wrote: > But how you handle situation when you have multi language application > which needs correct output for all languages? You cannot use any of the > pattern because it will be wrong for some group of languages. > FYI, strftime() [1] doesn't say anything about capitalization. I don't know if some translators are aware of it and even if they are, how would they know that a day or month is the first word of a sentence? IMHO we should provide Xxxxx, xxxxx, and XXXXX so the programmer can use whatever he/she thinks is correct for him/her. [1] http://www.opengroup.org/onlinepubs/009695399/functions/strftime.html -- Euler Taveira de Oliveira http://www.timbira.com/
Euler Taveira de Oliveira <euler@timbira.com> writes:
> FYI, strftime() [1] doesn't say anything about capitalization. I don't
> know if some translators are aware of it and even if they are, how would
> they know that a day or month is the first word of a sentence? IMHO we
> should provide Xxxxx, xxxxx, and XXXXX so the programmer can use
> whatever he/she thinks is correct for him/her.
I think you've missed the point, which is that the programmer can't
possibly know in advance which capitalization is correct, if she's
trying to build a system that works for different localizations.
But as Peter remarks nearby, this discussion is wasted anyway, because
there is only one correct answer: whatever Oracle does with these
cases is what to_char() should do.
regards, tom lane
> This is what you get when you copy a proprietary, poorly > specified interface. > The to_char functions are supposed to be Oracle-compatible, > so we need to check what Oracle does. Whether that is useful > in practice is a bit secondary. > > I'm beginning to think, if you want formatting functions that > are more sane, stable, and standardized, export sprintf and > strftime to PostgreSQL. Perhaps this is good time to think about (I'm being careful to say redesign) a review both regarding date/time formatting and locale handling for these functions in general. Regards, Gevik
> But as Peter remarks nearby, this discussion is wasted > anyway, because there is only one correct answer: whatever > Oracle does with these cases is what to_char() should do. ++1
Tom Lane wrote: > But as Peter remarks nearby, this discussion is wasted anyway, because > there is only one correct answer: whatever Oracle does with these > cases is what to_char() should do. > My patch does exactly what Oracle does besides one thing: my code does not contain a real capitalization function. It sucks when we have composite names like the portuguese above. I'll post an updated version later. SQL> SELECT TO_CHAR(SYSDATE, 'DY dy DAY Day day MONTH Month MON Mon mon', 'NLS_DATE_LANGUAGE = czech') FROM dual; TO_CHAR(SYSDATE,'DYDYDAYDAYDAYMONTHMONTHMONMONMON','NLS_DAT ----------------------------------------------------------- UT ut UTERY Utery utery UNOR Unor UNO Uno uno SQL> SELECT TO_CHAR(SYSDATE, 'DY dy DAY Day day MONTH Month MON Mon mon', 'NLS_DATE_LANGUAGE = dutch') FROM dual; TO_CHAR(SYSDATE,'DYDYDAYDAYDAYMONTHMONTHMONMONMON','NLS_DATE_LANGUA ------------------------------------------------------------------- DI di DINSDAG Dinsdag dinsdag FEBRUARI Februari FEB Feb feb SQL> SELECT TO_CHAR(SYSDATE, 'DY dy DAY Day day MONTH Month MON Mon mon', 'NLS_DATE_LANGUAGE = portuguese') FROM dual; TO_CHAR(SYSDATE,'DYDYDAYDAYDAYMONTHMONTHMONMONMON','NLS_DATE_LANGUAGE=PORTUGUESE' --------------------------------------------------------------------------------- TER ter TERCA-FEIRA Terca-Feira terca-feira FEVEREIRO Fevereiro FEV Fev fev -- Euler Taveira de Oliveira http://www.timbira.com/
Peter Eisentraut napsal(a):
> Am Dienstag, 26. Februar 2008 schrieb Zdenek Kotala:
>
>> But how you handle situation when you have multi language application
>> which needs correct output for all languages? You cannot use any of the
>> pattern because it will be wrong for some group of languages.
>>
>
> This is what you get when you copy a proprietary, poorly specified interface.
> The to_char functions are supposed to be Oracle-compatible, so we need to
> check what Oracle does. Whether that is useful in practice is a bit
> secondary.
>
I see. It is ok from this point of view.
> I'm beginning to think, if you want formatting functions that are more sane,
> stable, and standardized, export sprintf and strftime to PostgreSQL
Yes, it should solve a problem.
Zdenek
Euler Taveira de Oliveira wrote: > Tom Lane wrote: > > > But as Peter remarks nearby, this discussion is wasted anyway, because > > there is only one correct answer: whatever Oracle does with these > > cases is what to_char() should do. > > > My patch does exactly what Oracle does besides one thing: my code does > not contain a real capitalization function. It sucks when we have > composite names like the portuguese above. I'll post an updated version > later. Euler, have you updated this patch yet? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Euler, have you updated this patch yet? > I'm in a process to submit another patch but I want to test on Windows first. -- Euler Taveira de Oliveira http://www.timbira.com/
Bruce Momjian wrote:
> Euler, have you updated this patch yet?
>
Here is an updated patch. It follows the Oracle behaviour and uses a
cache mechanism to avoid calling setlocale() all the time. I unified the
localized_* and str_* functions. I didn't test it on Windows. I would
appreciate some feedback.
euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
to_char
-----------------------------------
thu Qui Quinta apr ABR abril 2008
(1 registro)
euler=# set lc_time to 'it_IT.UTF-8';
SET
euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
to_char
-------------------------------------
thu Gio Giovedì apr APR aprile 2008
(1 registro)
euler=# set lc_time to 'es_ES.UTF-8';
SET
euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
to_char
-----------------------------------
thu Jue Jueves apr ABR abril 2008
(1 registro)
euler=# set lc_time to 'fr_FR.UTF-8';
SET
euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
to_char
----------------------------------
thu Jeu Jeudi apr AVR avril 2008
(1 registro)
euler=# set lc_time to 'cs_CZ.UTF-8';
SET
euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
to_char
-----------------------------------
thu Čt Čtvrtek apr DUB duben 2008
(1 registro)
--
Euler Taveira de Oliveira
http://www.timbira.com/
Attachment
Euler Taveira de Oliveira wrote: > Here is an updated patch. It follows the Oracle behaviour and uses a > cache mechanism to avoid calling setlocale() all the time. I unified the > localized_* and str_* functions. I didn't test it on Windows. I would > appreciate some feedback. Added to May commitfest. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I have reviewed this patch. I like the method you used, but I did find
a few things I had to change.
First, I found the abbreviated variable names confusing so I used longer
ones, like:
extern char *localized_abbrev_days[7];
extern char *localized_full_days[7];
extern char *localized_abbrev_months[12];
extern char *localized_full_months[12];
Second, I found that the code doing upper/lower case didn't work. It
was copying the buffer into a 'result' variable, but then incrementing
'result' so by the end 'result' pointed to only the null byte, and that
is the pointer that was returned. Third, there were a few places where
the code assumed str_toupper() modified the passed buffer, rather than
returning a new one. And finally, while you used strdup() to save
values in the cache (good), you never free()'ed the old values when you
were reloading the cache.
I have fixed all these items and the updated patch is at:
ftp://momjian.us/pub/postgresql/mypatches/lc_time
The original patch was here:
http://archives.postgresql.org/message-id/481011DC.3050900@timbira.com
---------------------------------------------------------------------------
Euler Taveira de Oliveira wrote:
> Bruce Momjian wrote:
>
> > Euler, have you updated this patch yet?
> >
> Here is an updated patch. It follows the Oracle behaviour and uses a
> cache mechanism to avoid calling setlocale() all the time. I unified the
> localized_* and str_* functions. I didn't test it on Windows. I would
> appreciate some feedback.
>
>
> euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
> to_char
> -----------------------------------
> thu Qui Quinta apr ABR abril 2008
> (1 registro)
>
> euler=# set lc_time to 'it_IT.UTF-8';
> SET
> euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
> to_char
> -------------------------------------
> thu Gio Gioved? apr APR aprile 2008
> (1 registro)
>
> euler=# set lc_time to 'es_ES.UTF-8';
> SET
> euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
> to_char
> -----------------------------------
> thu Jue Jueves apr ABR abril 2008
> (1 registro)
>
> euler=# set lc_time to 'fr_FR.UTF-8';
> SET
> euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
> to_char
> ----------------------------------
> thu Jeu Jeudi apr AVR avril 2008
> (1 registro)
>
> euler=# set lc_time to 'cs_CZ.UTF-8';
> SET
> euler=# select to_char(now(), 'dy tmDy tmDay mon tmMON tmmonth YYYY');
> to_char
> -----------------------------------
> thu ?t ?tvrtek apr DUB duben 2008
> (1 registro)
>
>
> --
> Euler Taveira de Oliveira
> http://www.timbira.com/
[ application/x-gzip is not supported, skipping... ]
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > I have reviewed this patch. I like the method you used, but I did find > a few things I had to change. > Good catch. I tested here and it seems ok. Thanks for your review. -- Euler Taveira de Oliveira http://www.timbira.com/
Bruce Momjian <bruce@momjian.us> writes:
> I have fixed all these items and the updated patch is at:
> ftp://momjian.us/pub/postgresql/mypatches/lc_time
Applied with further fixes --- mostly, ensure that it doesn't leave a
crash-inducing corrupted cache if strdup() returns NULL, and make the
str_initcap routine respect current string conversion methods.
(str_initcap was outright wrong anyway since it assumed the output
must be the same byte length as the input.)
regards, tom lane