Обсуждение: datestyle issue. Is it always set to ISO, DMY ?

Hello,
I've seen that what I'm asking was discussed few years ago (2006) on this list

http://archives.postgresql.org/pgadmin-support/2006-01/msg00042.php

but I would like to know if it is still the same.

The problem (or the issue or whatever) is that (I think) pgadminIII
doesn't recognize the variable datestyle, so it always sets it to:ISO, DMY.

So when you are exploring your data with the pgadmin tools you will
always see dates formatted as
yyyy-mm-dd

But, if you are on the query tool you can change this behaviour with
(for example)
set datestyle = "postgres, euro";

and then if you select a column with dates they will formatted the european way
dd-mm-yyyy


I've changed the datesyle in the configuration file (postgresql.conf),
so if I work from the command line with psql it shows dates as
expected (postgres style), but inside pgadmin it always show
yyyy-mm-dd even-thought you add the datestyle variable in the database
properties tab also.

Am I missing something?
Is this a correct behaviour?


My environment is:
- Linux (ubuntu) lucid
- postgresql 8.4
- pgadmin 1.10.2 (Mar 23 2010, rev: 8217)


Thanks,
-- 
* Ermengol *
*************************************************
* Els ordinadors no resolen problemes, *
* simplement executen solucions           *
*************************************************

Hi,

Le 19/05/2010 00:15, Ermengol Bota a écrit :
> [...]
> I've seen that what I'm asking was discussed few years ago (2006) on this list
> 
> http://archives.postgresql.org/pgadmin-support/2006-01/msg00042.php
> 
> but I would like to know if it is still the same.
> 

Yes.

> The problem (or the issue or whatever) is that (I think) pgadminIII
> doesn't recognize the variable datestyle, so it always sets it to:
>  ISO, DMY.
> 

It's not that it doesn't recognize it. It is that it changes it to ISO
on all its connections.

> So when you are exploring your data with the pgadmin tools you will
> always see dates formatted as
> yyyy-mm-dd
> 

You're right.

> But, if you are on the query tool you can change this behaviour with
> (for example)
> set datestyle = "postgres, euro";
> 
> and then if you select a column with dates they will formatted the european way
> dd-mm-yyyy
> 

Sure, you bypassed pgAdmin session configuration, so pgAdmin displayed
dates in the datestyle you specified on the session.

pgAdmin got a result from PostgreSQL and it displays it in the same way.
It doesn't change the string it receives. It displays ISO by default
because, when it connects to a database, the first query it launches is
a "SET DateStyle=ISO;". It'll display with another style if you changes
the DateStyle parameter of the session.

> I've changed the datesyle in the configuration file (postgresql.conf),
> so if I work from the command line with psql it shows dates as
> expected (postgres style), but inside pgadmin it always show
> yyyy-mm-dd even-thought you add the datestyle variable in the database
> properties tab also.
> 

The datestyle property of a database is what you have if you don't
overrule it by a session changes.

> Am I missing something?
> Is this a correct behaviour?

At least, it's what's intended.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com

2010/5/19 Guillaume Lelarge <guillaume@lelarge.info>:
> Le 19/05/2010 00:15, Ermengol Bota a écrit :
> ...
>> But, if you are on the query tool you can change this behaviour with
>> (for example)
>> set datestyle = "postgres, euro";
>>
>> and then if you select a column with dates they will formatted the european way
>> dd-mm-yyyy
>> ...
>
> Sure, you bypassed pgAdmin session configuration, so pgAdmin displayed
> dates in the datestyle you specified on the session.
>
> pgAdmin got a result from PostgreSQL and it displays it in the same way.
> It doesn't change the string it receives. It displays ISO by default
> because, when it connects to a database, the first query it launches is
> a "SET DateStyle=ISO;". It'll display with another style if you changes
> the DateStyle parameter of the session.


First of all, thank you  for your fast answer and clear explanations.
Second :-), I'm not a database administrator, I'm just a teacher on a
software engineering subject at the university where we use postgres.
So, you are the experts, and probably the real users :-)

But, there is one thing that I don't understand.
I agree that is a good convention to force the datestyle wherever
pgadmin is running (even though when using a JDBC connection it
usually sets datestyle according to the SO locale or the one indicated
in the connection string), this way, pgadmin will always show the
dates the same way. Ok, no problem :-)

But, as far as you can set DataStyle variable for each database, maybe
could be a good idea that when connecting to a database that has this
variable setted not to "overwrite" it with the default (ISO).

( ALTER DATABASE aDtabase SET DateStyle='postgres, euro'; )


The idea is that if a database has the variable datestyle setted use
it, otherwise use ISO

Well, it's just an opinion :-)

And thanks for this great tool!

--
* Ermengol *
*************************************************
* Els ordinadors no resolen problemes, *
* simplement executen solucions           *
*************************************************