Thread: How to get DATE in server locale format
Server lc_times contains non-US locale. SELECT CURRENT_DATE::TEXT still returns date in format YYYY-MM-DD How to get date in server locale format ? Andrus.
On Sunday 10. January 2010 22.57.38 Andrus wrote: > Server lc_times contains non-US locale. > > SELECT CURRENT_DATE::TEXT > > still returns date in format YYYY-MM-DD > > How to get date in server locale format ? Pg doesn't care about lc_times. http://www.postgresql.org/docs/current/static/datatype-datetime.html#datatype- datetime-output2-table pgslekt=> SELECT CURRENT_DATE::TEXT; text ------------ 2010-01-11 (1 row) pgslekt=> set datestyle to german; SET pgslekt=> SELECT CURRENT_DATE::TEXT; text ------------ 11.01.2010 (1 row) regards, -- Leif Biberg Kristensen http://solumslekt.org/
"Andrus" <kobruleht2@hot.ee> writes:
> How to get date in server locale format ?
You might be able to get what you want with the to_char() function,
if setting datestyle doesn't do the trick for you.
regards, tom lane
> You might be able to get what you want with the to_char() function, > if setting datestyle doesn't do the trick for you. setting datestyle changes style for whole sql statement. How to get this in a single conversion in sql statement so that other expressions in same sql statement are not affected. to_char() requires hard-coded format. Different servers have different locales so that format is not know at application design time. How to get localized date for single conversion inside SELECT statement so that it works in different server lc_time settings ? Andrus.
In response to Andrus :
> >You might be able to get what you want with the to_char() function,
> >if setting datestyle doesn't do the trick for you.
>
> setting datestyle changes style for whole sql statement.
>
> How to get this in a single conversion in sql statement so that other
> expressions in same sql statement are not affected.
>
> to_char() requires hard-coded format.
> Different servers have different locales so that format is not know at
> application design time.
>
> How to get localized date for single conversion inside SELECT statement so
> that it works in different server lc_time settings ?
As Tom said, you can use to_char():
test=*# show lc_time;
lc_time
-------------
en_US.UTF-8
(1 row)
test=*# select to_char(current_date, 'TMDay - TMMonth - YYYY');
to_char
-------------------------
Monday - January - 2010
(1 row)
test=*# set lc_time = 'de_DE.UTF-8';
SET
test=*# select to_char(current_date, 'TMDay - TMMonth - YYYY');
to_char
------------------------
Montag - Januar - 2010
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 11/01/2010 9:44 PM, A. Kretschmer wrote:
> In response to Andrus :
>> How to get localized date for single conversion inside SELECT statement so
>> that it works in different server lc_time settings ?
>
> As Tom said, you can use to_char():
It looks like the OP wants a localized date, just one different to that
specified by the datestyle GUC, for just one date-to-string conversion
within a function that may have others.
Essentially, I think they're after
SET datestyle = DMY;
SELECT date_with_style(somedate, 'MDY'), somedate::text;
... which doesn't exist.
Andrus: Is the date style you need for the one "different" call fixed?
Or does it vary?
If it's fixed, you *can* just use to_char with a constant format string.
If the format needed for the "different" call varies, you'll have to
either have your app send a suitable to_char format string, or you'll
need to write a function that accepts a Pg datestyle parameter and
internally calls to_char with the appropriate format depending on the
value of the passed datestyle.
There's no way to say:
-- This does not work
SELECT CAST(somedate AS text WITH DATESTYLE 'iso')
or the like.
--
Craig Ringer