Обсуждение: TODO item:Allow to_date() and to_timestamp() accept localized month names

Hi,

I would like to start a discussion for a solution regarding this item.

At this moment these functions only accept English month/day names due
formatting.c:172:months_full[] and datetime.c:53-58 months[], days[].
The values are predetermined. (hardcoded sounds bahhh...)

What do we think about a solution that would be like:

1. Add an extra (optional) parameter to to_date and to_timestamp which would
indicate the locale we are trying to parse. 
For example to_date('10 okt 2008','DD Mon YYYY','nl-NL')

2. If the third parameter exists, read the month/day names for a list of
some kind.

3. Pass the list to formatting.c:1615:seq_search to parse the string with
these localized names list.

My questions:

Is step 1 acceptable/correct to start with? If yes, what would you recommend
for step 2? 

Regards,
Gevik Babakhani
------------------------------------------------
PostgreSQL NL       http://www.postgresql.nl
TrueSoftware BV     http://www.truesoftware.nl
------------------------------------------------

"Gevik Babakhani" <pgdev@xs4all.nl> writes:
> What do we think about a solution that would be like:

> 1. Add an extra (optional) parameter to to_date and to_timestamp which would
> indicate the locale we are trying to parse. 

Surely it should be the inverse of the solution for output, eg TMMon
selects localized input.
        regards, tom lane

> Surely it should be the inverse of the solution for output, 
> eg TMMon selects localized input.

Of cource. But how would TM enforce a localized formatting. (perhaps I am
off....  2:10 am...)
Lets say I have en_US database but the dates I am trying to format is nl_NL.
If I am not mistaking SET LC_MESSAGES won't help.

Regards,
Gevik

"Gevik Babakhani" <pgdev@xs4all.nl> writes:
>> Surely it should be the inverse of the solution for output, 
>> eg TMMon selects localized input.

> Of cource. But how would TM enforce a localized formatting. (perhaps I am
> off....  2:10 am...)
> Lets say I have en_US database but the dates I am trying to format is nl_NL.
> If I am not mistaking SET LC_MESSAGES won't help.

Works for me:

postgres=# show lc_messages ;lc_messages 
-------------de_DE.utf8
(1 row)

postgres=# select to_char(now(), 'TMDay, DD TMMonth YYYY');        to_char         
-------------------------Montag, 04 Februar 2008
(1 row)

postgres=# set lc_messages TO 'es_ES.utf8';
SET
postgres=# select to_char(now(), 'TMDay, DD TMMonth YYYY');       to_char         
------------------------Lunes, 04 Febrero 2008
(1 row)

        regards, tom lane

Tom Lane wrote:
> "Gevik Babakhani" <pgdev@xs4all.nl> writes:
> >> Surely it should be the inverse of the solution for output, 
> >> eg TMMon selects localized input.
> 
> > Of cource. But how would TM enforce a localized formatting. (perhaps I am
> > off....  2:10 am...)
> > Lets say I have en_US database but the dates I am trying to format is nl_NL.
> > If I am not mistaking SET LC_MESSAGES won't help.
> 
> Works for me:

Nevertheless, I think there's something interesting missing here, which
is a sort of strftime's %c format string.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Nevertheless, I think there's something interesting missing here, which
> is a sort of strftime's %c format string.

Perhaps, but let us please not cram random non-Oracle-compatible stuff
into to_date/to_char.  Those have a charter already.
        regards, tom lane

Alvaro Herrera wrote:
> Nevertheless, I think there's something interesting missing here, which
> is a sort of strftime's %c format string.

I think the Oracle way to do that would be to_char() with one argument and 
setting NLS_DATE_FORMAT.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

(I really should stop reading the code after 12:00AM)

So if I understand correctly, the proper solution would be to handle the
localized (TM) format
within to_date (seq_search). This means that prior calling to_date a SET
LC_MESSAGES must be given.
but if we are following Oracle,
(http://www.techonthenet.com/oracle/functions/to_date.php) a third parameter
to enforce the nls_language is required. Please advice.

Regards,
Gevik.


> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: Tuesday, February 05, 2008 2:29 AM
> To: Gevik Babakhani
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] TODO item:Allow to_date() and 
> to_timestamp() accept localized month names 
> 
> "Gevik Babakhani" <pgdev@xs4all.nl> writes:
> >> Surely it should be the inverse of the solution for 
> output, eg TMMon 
> >> selects localized input.
> 
> > Of cource. But how would TM enforce a localized formatting. 
> (perhaps I 
> > am off....  2:10 am...) Lets say I have en_US database but 
> the dates I 
> > am trying to format is nl_NL.
> > If I am not mistaking SET LC_MESSAGES won't help.
> 
> Works for me:
> 
> postgres=# show lc_messages ;
>  lc_messages
> -------------
>  de_DE.utf8
> (1 row)
> 
> postgres=# select to_char(now(), 'TMDay, DD TMMonth YYYY');
>          to_char         
> -------------------------
>  Montag, 04 Februar 2008
> (1 row)
> 
> postgres=# set lc_messages TO 'es_ES.utf8'; SET postgres=# 
> select to_char(now(), 'TMDay, DD TMMonth YYYY');
>         to_char         
> ------------------------
>  Lunes, 04 Febrero 2008
> (1 row)
> 
> 
>             regards, tom lane
> 

> 
> Surely it should be the inverse of the solution for output, 
> eg TMMon selects localized input.
> 

After some investigation in how gettext works, I would like to have your
opinion about how to 
implement this TODO item.

Starting with TO_CHAR: 

When the TM prefix is used in TO_CHAR (for example TMMonth),
the routine, internally calls functions like the localize_month and
localize_day to get the localized value.
These functions rely on the current locale category that is internally
loaded by GetText "engine". 
The GetText engine does not load any other locate category unless SET
LC_MESSAGES or alike is given.


Now back to TO_DATE: 

For this there are three solution that I can think of.

1. For TO_DATE to return localized data we can implement the TM prefix logic
which is already
implemented in TO_CHAR. Copying and modifying it for TO_DATE should be
feasible.
The downside of this solution is that TO_DATE will only return localized
values base on current locale. (no third parameter)

2. For TO_DATE to behave like Mr. Oracle's version of to_date (
to_date('01-OCT-99''DD-MON-YY,'nls_date_language = nl_NL'); ) We might (must
be tested to see if it is even possible) be able to have a smaller copy of
gettext engine that only contains day and month names and have TO_DATE use
it to return values based on the given locale. This solution is much harder
to implement of course.

3. Have TO_DATE to switch the locale back and forth by internally executing
SET LC_* to the given locale.
Please note that loading another locale category forced by SET LC_* is
costly. (look at GetText source). 

Any thoughts?

Regards,
Gevik Babakhani
------------------------------------------------
PostgreSQL NL       http://www.postgresql.nl
TrueSoftware BV     http://www.truesoftware.nl
------------------------------------------------




> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org 
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of 
> Peter Eisentraut
> Sent: Tuesday, February 05, 2008 9:04 AM
> To: pgsql-hackers@postgresql.org
> Cc: Alvaro Herrera; Tom Lane; Gevik Babakhani
> Subject: Re: [HACKERS] TODO item:Allow to_date() and 
> to_timestamp() accept localized month names
> 
> Alvaro Herrera wrote:
> > Nevertheless, I think there's something interesting missing here, 
> > which is a sort of strftime's %c format string.
> 
> I think the Oracle way to do that would be to_char() with one 
> argument and setting NLS_DATE_FORMAT.
> 
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 6: explain analyze is your friend

Gevik Babakhani wrote:
> > 
> > Surely it should be the inverse of the solution for output, 
> > eg TMMon selects localized input.
> > 
> 
> After some investigation in how gettext works, I would like to have your
> opinion about how to 
> implement this TODO item.
> 
> Starting with TO_CHAR: 
> 
> When the TM prefix is used in TO_CHAR (for example TMMonth),
> the routine, internally calls functions like the localize_month and
> localize_day to get the localized value.
> These functions rely on the current locale category that is internally
> loaded by GetText "engine". 
> The GetText engine does not load any other locate category unless SET
> LC_MESSAGES or alike is given.
> 
> 
> Now back to TO_DATE: 
> 
> For this there are three solution that I can think of.
> 
> 1. For TO_DATE to return localized data we can implement the TM prefix logic
> which is already
> implemented in TO_CHAR. Copying and modifying it for TO_DATE should be
> feasible.
> The downside of this solution is that TO_DATE will only return localized
> values base on current locale. (no third parameter)

I can't remember anyone asking for more sophisticated solutions so it
seems implementing #1 at this point is the best approach.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

> I can't remember anyone asking for more sophisticated 
> solutions so it seems implementing #1 at this point is the 
> best approach.
> 

OK. Then I'll start working on the first approach.

Regards,
Gevik.