Обсуждение: 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.
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
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.
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
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