Thread: How to convert US date format to European date format ?

How to convert US date format to European date format ?

From
Condor
Date:
Hello ppl,

I have a database where the previous owner use US date format in date
fields:

2009-02-18

Is there a way how to convert the fields in European format 18-02-2009.
I mean existing date in records. What's will be happened if I change
format
in postgresql.conf ?


Cheers,
Hristo S.


Re: How to convert US date format to European date format ?

From
Thomas Kellerer
Date:
Condor, 10.04.2013 15:03:
> Hello ppl,
>
> I have a database where the previous owner use US date format in date fields:
>
> 2009-02-18
>
> Is there a way how to convert the fields in European format 18-02-2009.
> I mean existing date in records. What's will be happened if I change format
> in postgresql.conf ?

A date column does NOT have a format.

The format is only applied by the client application when _displaying_ the date.

Btw. 2009-02-18 is not the US format, it's the ISO format, in the US the format 02/18/2012 is used.

psql (one of the possible client applications) uses the "datestyle" parameter to decide on how to format a date column
whendisplaying it. 

If you change the "datestyle" parameter in postgresql.conf, it will influence the way psql displays the date values.
ProbablypgAdmin will also check that setting (as I don't use pgAdmin I can't really tell). 

Thomas


Re: How to convert US date format to European date format ?

From
Adrian Klaver
Date:
On 04/10/2013 06:03 AM, Condor wrote:
> Hello ppl,
>
> I have a database where the previous owner use US date format in date
> fields:
>
> 2009-02-18
>
> Is there a way how to convert the fields in European format 18-02-2009.
> I mean existing date in records. What's will be happened if I change format
> in postgresql.conf ?

The dates are stored as a non formatted value. What you are seeing is
the output formatting:

http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

In other words the data in the fields will not be converted.

If you want a European style formatting then you can set the
DateStyle='SQL,DMY':

test=> set datestyle='SQL,DMY';
SET
test=> SELECT now()::date;
     now
------------
  10/04/2013
(1 row)


This can be done as needed or by setting it in postgresql.conf

>
>
> Cheers,
> Hristo S.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: How to convert US date format to European date format ?

From
John R Pierce
Date:
On 4/10/2013 6:15 AM, Thomas Kellerer wrote:
> psql (one of the possible client applications) uses the "datestyle"
> parameter to decide on how to format a date column when displaying it.
>
> If you change the "datestyle" parameter in postgresql.conf, it will
> influence the way psql displays the date values. Probably pgAdmin will
> also check that setting (as I don't use pgAdmin I can't really tell).

PSQL doesn't use that, postgres itself does.   it can be set on the fly
with SET on a per-connection basis, or with ALTER DATABASE on a
per-database basis.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



Re: How to convert US date format to European date format ?

From
Thomas Kellerer
Date:
John R Pierce wrote on 10.04.2013 21:28:
> On 4/10/2013 6:15 AM, Thomas Kellerer wrote:
>> psql (one of the possible client applications) uses the "datestyle"
>> parameter to decide on how to format a date column when displaying
>> it.
>>
>> If you change the "datestyle" parameter in postgresql.conf, it will
>> influence the way psql displays the date values. Probably pgAdmin
>> will also check that setting (as I don't use pgAdmin I can't really
>> tell).
>
> PSQL doesn't use that, postgres itself does.   it can be set on the
> fly with SET on a per-connection basis, or with ALTER DATABASE on a
> per-database basis.


But the *display* is done by the client.
And if Postgres (the server) did the conversion, I would not be able to see a different date formatting in e.g. a JDBC
basedtool. So I guess psql is reading that database/server setting. 



Re: How to convert US date format to European date format ?

From
John R Pierce
Date:
On 4/10/2013 12:35 PM, Thomas Kellerer wrote:
But the *display* is done by the client.
And if Postgres (the server) did the conversion, I would not be able to see a different date formatting in e.g. a JDBC based tool. So I guess psql is reading that database/server setting.

psql is letting postgres send it as text rather than the binary internal date format.  

JDBC has its own extensive date munging with its own database independent rules.  in particular, it does NOT use libpq.




-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: How to convert US date format to European date format?

From
Condor
Date:
On 2013-04-10 22:35, Thomas Kellerer wrote:
> John R Pierce wrote on 10.04.2013 21:28:
>> On 4/10/2013 6:15 AM, Thomas Kellerer wrote:
>>> psql (one of the possible client applications) uses the "datestyle"
>>> parameter to decide on how to format a date column when displaying
>>> it.
>>>
>>> If you change the "datestyle" parameter in postgresql.conf, it will
>>> influence the way psql displays the date values. Probably pgAdmin
>>> will also check that setting (as I don't use pgAdmin I can't really
>>> tell).
>>
>> PSQL doesn't use that, postgres itself does.   it can be set on the
>> fly with SET on a per-connection basis, or with ALTER DATABASE on a
>> per-database basis.
>
>
> But the *display* is done by the client.
> And if Postgres (the server) did the conversion, I would not be able
> to see a different date formatting in e.g. a JDBC based tool. So I
> guess psql is reading that database/server setting.


Hello again,
what parameter should I use to have date in format:

dd-mm-yyyy ? I try to use Posgtgres, DMY and it's seems is work,
but not in my case, because I have also a field:

last_date timestamp without time zone default
('now'::text)::timestamp(6) with time zone

and ISO, DMY show me: 2012-10-15 11:00:49.397908 if I use Postgres, DMY
show me
Mon 15 Oct 11:00:49.397908 2012

But I want to be formatted: 11:00:49 15-10-2012

Is this possible to be done ?

Cheers,
Hristo S.


Re: How to convert US date format to European date format ?

From
John R Pierce
Date:
On 4/12/2013 12:42 AM, Condor wrote:
> and ISO, DMY show me: 2012-10-15 11:00:49.397908 if I use Postgres,
> DMY show me
> Mon 15 Oct 11:00:49.397908 2012
>
> But I want to be formatted: 11:00:49 15-10-2012

use the date formatting functions, like...

     select to_char(yourfield, 'HH:MI:SS DD-MM-YYYY') ...

see http://www.postgresql.org/docs/current/static/functions-formatting.html




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



Re: How to convert US date format to European date format?

From
Condor
Date:
On 2013-04-12 10:59, John R Pierce wrote:
> On 4/12/2013 12:42 AM, Condor wrote:
>> and ISO, DMY show me: 2012-10-15 11:00:49.397908 if I use Postgres,
>> DMY show me
>> Mon 15 Oct 11:00:49.397908 2012
>>
>> But I want to be formatted: 11:00:49 15-10-2012
>
> use the date formatting functions, like...
>
>     select to_char(yourfield, 'HH:MI:SS DD-MM-YYYY') ...
>
> see
> http://www.postgresql.org/docs/current/static/functions-formatting.html
>
>
>
>
> --
> john r pierce                                      37N 122W
> somewhere on the middle of the left coast

Yes, I see this function but if I need to select 100 000 rows this mean
I think,
this function will be start 100 000 times. I mean when I ask the
question,
it's is possible to format the date how I like it without to use
functions,
just something like: set datestyle ('postgres with my custom format
00:00:00 dmy', DMY)
something like that. Sry that I did not explain it.


Cheers,
Hristo S.


Re: How to convert US date format to European date format ?

From
Pavel Stehule
Date:



2013/4/12 Condor <condor@stz-bg.com>
On 2013-04-12 10:59, John R Pierce wrote:
On 4/12/2013 12:42 AM, Condor wrote:
and ISO, DMY show me: 2012-10-15 11:00:49.397908 if I use Postgres, DMY show me
Mon 15 Oct 11:00:49.397908 2012

But I want to be formatted: 11:00:49 15-10-2012

use the date formatting functions, like...

    select to_char(yourfield, 'HH:MI:SS DD-MM-YYYY') ...

see http://www.postgresql.org/docs/current/static/functions-formatting.html




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Yes, I see this function but if I need to select 100 000 rows this mean I think,
this function will be start 100 000 times. I mean when I ask the question,
it's is possible to format the date how I like it without to use functions,
just something like: set datestyle ('postgres with my custom format 00:00:00 dmy', DMY)
something like that. Sry that I did not explain it.


If you cannot use datestyle, then there are no any other possibility. Postgres doesn't support custom datestyles.

Regards

Pavel
 

Cheers,
Hristo S.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: How to convert US date format to European date format ?

From
Adrian Klaver
Date:
On 04/12/2013 01:54 AM, Condor wrote:

>>
>>
>>
>> --
>> john r pierce                                      37N 122W
>> somewhere on the middle of the left coast
>
> Yes, I see this function but if I need to select 100 000 rows this mean
> I think,
> this function will be start 100 000 times. I mean when I ask the question,
> it's is possible to format the date how I like it without to use functions,
> just something like: set datestyle ('postgres with my custom format
> 00:00:00 dmy', DMY)
> something like that. Sry that I did not explain it.

FYI, DateStyle uses functions also, as the stored date has to be
reformatted to whatever style is chosen. See datetime.c in the source
for the functions. I would try to_char() and see if it makes a
discernible difference in the select.

>
>
> Cheers,
> Hristo S.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com