Localization (for dates) Oracle vs. Postgresql

I want to change for example for one session the date-format from english to german, so that
The month names for example march will be März in German.
I only need that for the monthnames.
Is this possible to make that for example for one session.

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Mittwoch, 3. September 2003 16:00
An: Amin Schoeib
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Localization (for dates) Oracle vs. Postgresql

"Amin Schoeib" <aschoeib@4tek.de> writes:

Now I want to know if it is possible (when yes the how?) in Postgresql
To set dynamically the Localization? For example in Oracle you can set
the Localization for dates like this

What exactly do you want to localize?

The DATESTYLE setting and the various LC_xxx settings might help. We do not have the ability to change LC_COLLATE or LC_CTYPE on the fly, unfortunately.

regards, tom lane

"Amin Schoeib" <aschoeib@4tek.de> writes:

I want to change for example for one session the date-format from english to german, so that
The month names for example march will be M�rz in German.
I only need that for the monthnames.

I think you can do this if you are willing to use to_char() to format
the dates for display. There's no provision for such localization in
the basic date or timestamp datatypes, though.

regards, tom lane

First of all I want to thank you for your quick response.

That would be very nice if it is possible.
But using to_char I can only set the format or is it in Postgres
Other?
When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Mittwoch, 3. September 2003 16:21
An: Amin Schoeib
Cc: pgsql-general@postgresql.org
Betreff: Re: AW: [GENERAL] Localization (for dates) Oracle vs. Postgresql

"Amin Schoeib" <aschoeib@4tek.de> writes:

I want to change for example for one session the date-format from
english to german, so that The month names for example march will be
März in German. I only need that for the monthnames.

I think you can do this if you are willing to use to_char() to format the dates for display. There's no provision for such localization in the basic date or timestamp datatypes, though.

regards, tom lane

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

Karel, isn't there a way to get localized month names using to_char() ?
I thought there was, but I don't see anything about it in the manual.

regards, tom lane

On Wed, Sep 03, 2003 at 10:36:29AM -0400, Tom Lane wrote:

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

Karel, isn't there a way to get localized month names using to_char() ?
I thought there was, but I don't see anything about it in the manual.

No way:-(

But I think it's good point to TODO of the 7.5 release.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Is there maybe any other???

-----Ursprüngliche Nachricht-----
Von: Karel Zak [mailto:zakkr@zf.jcu.cz]
Gesendet: Mittwoch, 3. September 2003 16:53
An: Tom Lane
Cc: Amin Schoeib; pgsql-general@postgresql.org
Betreff: Re: AW: AW: [GENERAL] Localization (for dates) Oracle vs. Postgresql

On Wed, Sep 03, 2003 at 10:36:29AM -0400, Tom Lane wrote:

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with
to_char That I become the monthname in german??

Karel, isn't there a way to get localized month names using to_char()
? I thought there was, but I don't see anything about it in the
manual.

No way:-(

But I think it's good point to TODO of the 7.5 release.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

"Amin Schoeib" <aschoeib@4tek.de> writes:

Is there maybe any other???

The only other suggestion I can make is to write your own formatting
function. It'd be a pain in the neck to build a general-purpose one,
but you could handle a fixed output format with just a few lines of code
in plpgsql. (Might be even easier in plperl or pltcl.) That would
probably tide you over until to_char() has more locale support.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Amin Schoeib" <aschoeib@4tek.de> writes:

Is there maybe any other???

The only other suggestion I can make is to write your own formatting
function. It'd be a pain in the neck to build a general-purpose one,
but you could handle a fixed output format with just a few lines of code
in plpgsql. (Might be even easier in plperl or pltcl.) That would
probably tide you over until to_char() has more locale support.

I was planning on doing a date formatting function that took an extra locale
parameter like my xfrm function. But I haven't gotten there yet. I have to
read up on strftime before I start too. I'm not clear how to handle the fact
that different locales prefer different orders for their fields.

--
greg

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

Karel, isn't there a way to get localized month names using
to_char() ? I thought there was, but I don't see anything about it
in the manual.

I sent the patch long time ago and it was rejected by Karel :-(. I can
work on this again but I can't remember exactly why it was rejected, I
think Karel and I didn't reach agreement on the API.

Regards,
Manuel.

Karel Zak wrote:

On Wed, Sep 03, 2003 at 10:36:29AM -0400, Tom Lane wrote:

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

Karel, isn't there a way to get localized month names using to_char() ?
I thought there was, but I don't see anything about it in the manual.

No way:-(

But I think it's good point to TODO of the 7.5 release.

Added to TODO:

* Allow to_char to print localized month names (Karel)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, Sep 03, 2003 at 05:04:35PM -0500, Manuel Sugawara wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

"Amin Schoeib" <aschoeib@4tek.de> writes:

When I would execute this:
select to_char(now(),'DD.Month,YYYY')
I would get the monthname in english but how can I perform with to_char
That I become the monthname in german??

Karel, isn't there a way to get localized month names using
to_char() ? I thought there was, but I don't see anything about it
in the manual.

I sent the patch long time ago and it was rejected by Karel :-(. I can
work on this again but I can't remember exactly why it was rejected, I
think Karel and I didn't reach agreement on the API.

Yes.

Note about to_char() future: I think about library for date/time and numbers
formatting, because it's feature interesting for more projects. The
basic features:

* better date/time, numbers and format pictures parser (maybe based on flex+bison)
* full locale support - independent on actual locale setting
* bidirectional conversion of roman numbers
* numbers spelling
* string to string formatting

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/