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

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.

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

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

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

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. 

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

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.

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

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.

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