Thread: 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 * *************************************************