Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 3:53 AM, Adrian Klaver adrian.kla...@aklaver.comwrote: Each driver will have its own behavior. For an explanation of the JDBC behavior see here: http://www.postgresql.org/message-id/4b2f2ced.10...@opencloud.com Per Andrews posts, the least surprise behavior is to explicitly set the client time zone. Then you control what is being seen/used. Actually then this goes back to the same thing that identify the timezone setting in OS and accordingly set at the driver level. In case of java JVM is picking up OS timezone and hence things are working without any issues for windows/linux both. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 3:55 AM, Terence Ferraro terencejferr...@gmail.comwrote: Or, if you don't mind a little patching: http://pastebin.com/5AyaX2RF That restores the pre-9.1 functionality of determining the timezone on postmaster start. As has been pointed out, their new stuff is more useful if you're shipping instances to the farthest reaches of the planet (We've only got clients in North America at the moment). You will undoubtedly need to make some modifications to patch with 9.3, but I've got hundreds of 9.2 instances in the field now that, well, just work. Thanks for sharing! So that means need to build the database all over again for both windows and linux. Actually have been using binaries and utilizing them for creating database. Regards...
Re: [GENERAL] Timezone information
inline: On Thu, Feb 20, 2014 at 3:57 AM, Tom Lane t...@sss.pgh.pa.us wrote: The functionality of determining an IANA timezone name equivalent to the platform's behavior is currently embedded in initdb and isn't separately accessible. So you've got several options: Hmm, actually was looking for exact that command which could resolve my requirement. But as you explained its not separately available. 1. Wait to do initdb until the machine is installed. No doubt this is solution and doable but doesn't fit into my current deployment model. 2. Modify the timezone setting in postgresql.conf after the machine is installed (although there might not be an easy way to determine what to set it to). Yes as mentioned earlier here is the POA - a perl function to get the timezone on both windows and linux then map them to the pg_timezone_names table to get actual names in format Asia/Calcutta and set this as timezone parameter in postgreSQL.conf to workaround things. 3. Leave the server timezone setting as GMT and rely on clients to select the zone they want to work in. JDBC there is no issue. However if there is any clue at ODBC driver level, please let know. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 4:31 AM, Adrian Klaver adrian.kla...@aklaver.comwrote: It depends on how you are declaring the timestamp field. If you do not use with time zone then the input value is open to interpretation and is not 'anchored' to a point in time. Example My time zone is currently PST. test= create table timestamp_test(id int, ts timestamp, ts_z timestamp with time zone); CREATE TABLE test= insert into timestamp_test values (1, now(), now()); INSERT 0 1 . If you know what time zone the value was inserted under you can get it back. . . That assumes a lot, so the best thing is to use timestamp with time zone. Thanks for trying this out on your setup. However looks like my requirement is different here. Regards...
Re: [GENERAL] Timezone information
On 02/20/2014 04:29 AM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 3:53 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: Each driver will have its own behavior. For an explanation of the JDBC behavior see here: http://www.postgresql.org/__message-id/4B2F2CED.10400@__opencloud.com http://www.postgresql.org/message-id/4b2f2ced.10...@opencloud.com Per Andrews posts, the least surprise behavior is to explicitly set the client time zone. Then you control what is being seen/used. Actually then this goes back to the same thing that identify the timezone setting in OS and accordingly set at the driver level. In case of java JVM is picking up OS timezone and hence things are working without any issues for windows/linux both. No it is the Postgres JDBC driver that is doing this. It seems the MySQL JDBC driver operated differently until recently: http://stackoverflow.com/questions/15206194/jdbc-mysql-save-timestamp-always-using-utc The point is, if you are counting on consistent behavior with regard to time in applications that touch the database, you will be disappointed. Regards... -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On 02/20/2014 04:59 AM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 4:31 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: It depends on how you are declaring the timestamp field. If you do not use with time zone then the input value is open to interpretation and is not 'anchored' to a point in time. Example My time zone is currently PST. test= create table timestamp_test(id int, ts timestamp, ts_z timestamp with time zone); CREATE TABLE test= insert into timestamp_test values (1, now(), now()); INSERT 0 1 . If you know what time zone the value was inserted under you can get it back. . . That assumes a lot, so the best thing is to use timestamp with time zone. Thanks for trying this out on your setup. However looks like my requirement is different here. So what is your requirement? Do you have a specific application/use for the databases you are installing? Regards... -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 9:30 PM, Adrian Klaver adrian.kla...@aklaver.comwrote: So what is your requirement? Do you have a specific application/use for the databases you are installing? There are two interfaces for the database. One is from the ODBC driver and other is from the JDBC driver. These are the two clients which I have currently who communicate with database. Regards...
Re: [GENERAL] Timezone information
On Fri, Feb 21, 2014 at 12:14:42AM +0530, Dev Kumkar wrote: These are the two clients which I have currently who communicate with database. Do you control the client code? If so, why not set the TimeZone locally when you connect? That's the right way to handle this, really. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Fri, Feb 21, 2014 at 12:43 AM, Andrew Sullivan a...@crankycanuck.cawrote: Do you control the client code? If so, why not set the TimeZone locally when you connect? That's the right way to handle this, really. Agree. So find the OS timezone at programming level and set accordingly. This can be done either in C for ODBC stuff and at java level. Although JVM has its own timezone database and sets accordingly. Regards...
Re: [GENERAL] Timezone information
Dev Kumkar devdas.kum...@gmail.com writes: How to set timezone in postgreSQL database to pick operating system level timezone information. If you mean you would like to use Windows' timezone data, the answer is you can't --- and you generally shouldn't want to, because AFAIK their timezone data is pretty sucky: it's incomplete and not terribly accurate about historical details. We use the IANA timezone database[1], which is where those names like Asia/Calcutta come from. Most modern operating systems use the IANA database for their system-level timezone knowledge, but Windows is still in the dark ages last I heard. regards, tom lane [1] http://www.iana.org/time-zones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Wed, Feb 19, 2014 at 8:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you mean you would like to use Windows' timezone data, the answer is you can't --- and you generally shouldn't want to, because AFAIK their timezone data is pretty sucky: it's incomplete and not terribly accurate about historical details. We use the IANA timezone database[1], which is where those names like Asia/Calcutta come from. Most modern operating systems use the IANA database for their system-level timezone knowledge, but Windows is still in the dark ages last I heard. regards, tom lane Hmm. Is there any postgreSQL command/binary which can be used to set timezone according to OS one. At the time of postgreSQL install how does it pick timezone information and sets into postgreSQL.conf accordingly. Regards...
Re: [GENERAL] Timezone information
On 2/19/2014 11:59 AM, Dev Kumkar wrote: Hmm. Is there any postgreSQL command/binary which can be used to set timezone according to OS one. At the time of postgreSQL install how does it pick timezone information and sets into postgreSQL.conf accordingly. postgres handles timezones on a per client connection basis. the server itself doesn't really care what timezone it is running in. 'timestamp with time zone' data is internally stored in the equivalent of UTC, and converted to/from the current client timezone on the fly (unless another timezone is explicitly stated in the query). -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 2:06 AM, John R Pierce pie...@hogranch.com wrote: postgres handles timezones on a per client connection basis. the server itself doesn't really care what timezone it is running in. 'timestamp with time zone' data is internally stored in the equivalent of UTC, and converted to/from the current client timezone on the fly (unless another timezone is explicitly stated in the query). Ok but am confused then, as if no timezone parameter is defined in postgreSQL.conf file then yes UTC time is returned. Select now() but if timezone parameter is set then Select now() returns date time as per timezone. Again one question which am still looking answer is, in case of install from postgreSQL installer how the timezone parameter in postgreSQL.conf is set correctly to match with Operating system timezone. Also note that, I can write a perl function to get the timezone on both windows and linux then map them to the pg_timezone_names table to get actual names in format Asia/Calcutta and set this as timezone parameter in postgreSQL.conf to workaround things. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 02:24:53AM +0530, Dev Kumkar wrote: Ok but am confused then, as if no timezone parameter is defined in postgreSQL.conf file then yes UTC time is returned. Select now() but if timezone parameter is set then Select now() returns date time as per timezone. Try issuing SET TimeZone commands. I think you'll find that the client can set whatever time zone it wants. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On 02/19/2014 01:02 PM, Andrew Sullivan wrote: On Thu, Feb 20, 2014 at 02:24:53AM +0530, Dev Kumkar wrote: Ok but am confused then, as if no timezone parameter is defined in postgreSQL.conf file then yes UTC time is returned. Select now() but if timezone parameter is set then Select now() returns date time as per timezone. Try issuing SET TimeZone commands. I think you'll find that the client can set whatever time zone it wants. I think what the OP wants is to know how Postgres sets TimeZone in postgresql.conf when a cluster is created via initdb on Windows? Best regards, A -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan a...@crankycanuck.cawrote: Try issuing SET TimeZone commands. I think you'll find that the client can set whatever time zone it wants. I think am not being heard incorrectly here. Here is the scenario - Database is created using binaries and not postgreSQL installer here. Next this database gets created on one machine SRC-INDIA and then is shipped globally which can be in any timezone, for example TGT-USA. Since the database is created on SRC-INDIA the timezone parameter gets set as Asia/Calcutta, but when shipped locally then now() would return incorrect time as per target TGT-USA local time. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 02:46:46AM +0530, Dev Kumkar wrote: Since the database is created on SRC-INDIA the timezone parameter gets set as Asia/Calcutta, but when shipped locally then now() would return incorrect time as per target TGT-USA local time. No, select now() would return the time in whatever timezone is set, or the timezone that the server defaulted to if there's nothing set by the client. So in your installation, set up the server to use UTC by default and, if you like, set the client's time zone according to locale or whatever when the client connects. The timestamps in the server are not actually in a time zone. They're all stored as UTC, and the display is altered according to what your time zone settings are at the time of query. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On 02/19/2014 01:16 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan a...@crankycanuck.ca mailto:a...@crankycanuck.ca wrote: Try issuing SET TimeZone commands. I think you'll find that the client can set whatever time zone it wants. I think am not being heard incorrectly here. Here is the scenario - Database is created using binaries and not postgreSQL installer here. Next this database gets created on one machine SRC-INDIA and then is shipped globally which can be in any timezone, for example TGT-USA. Since the database is created on SRC-INDIA the timezone parameter gets set as Asia/Calcutta, but when shipped locally then now() would return incorrect time as per target TGT-USA local time. Do you know where the machine is going when you do the install? Regards... -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan a...@crankycanuck.cawrote: No, select now() would return the time in whatever timezone is set, or the timezone that the server defaulted to if there's nothing set by the client. So in your installation, set up the server to use UTC by default and, if you like, set the client's time zone according to locale or whatever when the client connects. In my case the timezone parameter gets set. If I comment/remove it then UTC is returned by default. So server here defaulted to UTC. Executed Select now() from pgAdmin and psql, time gets returned in UTC. The timestamps in the server are not actually in a time zone. They're all stored as UTC, and the display is altered according to what your time zone settings are at the time of query. Hmm. Missed one observation here, created a test table with timestamp column of type 'default current_timestamp'. When the query is executed from JDBC then it stores OS specific local time into this column. However when the same query is executed from ODBC then it behaves either as per the timezone set in postgreSQL.conf or when not set then UTC. So looks like am missing some setting while executing query from ODBC. Btw also the pgAdmin and psql behave same as ODBC case. What am missing here which JDBC is doing correctly. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver adrian.kla...@aklaver.comwrote: Do you know where the machine is going when you do the install? No if that was the case then target could have set before shipping itself. Machine TGT-Region is not known here. Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 03:22:15AM +0530, Dev Kumkar wrote: Hmm. Missed one observation here, created a test table with timestamp column of type 'default current_timestamp'. When the query is executed from JDBC then it stores OS specific local time into this column. Probably the JDBC driver is setting its TimeZone. Really, try it: SET TimeZone=UTC; SELECT now(); SET TimeZone=EST5EDT; SELECT now(); and so on. Try selecting from your table, too, and you will discover that the time zone of the timestamps changes. If you're used to certain other RDBMSes, this mode of functioning will be unusual, but that really is how it works. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan a...@crankycanuck.cawrote: On Thu, Feb 20, 2014 at 03:22:15AM +0530, Dev Kumkar wrote: Hmm. Missed one observation here, created a test table with timestamp column of type 'default current_timestamp'. When the query is executed from JDBC then it stores OS specific local time into this column. Probably the JDBC driver is setting its TimeZone. Really, try it: SET TimeZone=UTC; SELECT now(); SET TimeZone=EST5EDT; SELECT now(); and so on. Try selecting from your table, too, and you will discover that the time zone of the timestamps changes. If you're used to certain other RDBMSes, this mode of functioning will be unusual, but that really is how it works. Yes had tried this earlier and it works as expected. I think I missed that observation earlier and then was looking to set timezone in postgreSQL.conf which could ultimately resolve this. But better is to set the TimeZone. Now haven't done anything special but JDBC is working with setting TimeZone and ODBC not. So what should I look from here now? Regards...
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 03:37:20AM +0530, Dev Kumkar wrote: But better is to set the TimeZone. Now haven't done anything special but JDBC is working with setting TimeZone and ODBC not. So what should I look from here now? I think your client should set the TimeZone at connection time. That's going to yield the most predictable behaviour for the users, I suspect. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On 02/19/2014 02:07 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan a...@crankycanuck.ca mailto:a...@crankycanuck.ca wrote: On Thu, Feb 20, 2014 at 03:22:15AM +0530, Dev Kumkar wrote: Hmm. Missed one observation here, created a test table with timestamp column of type 'default current_timestamp'. When the query is executed from JDBC then it stores OS specific local time into this column. Probably the JDBC driver is setting its TimeZone. Really, try it: SET TimeZone=UTC; SELECT now(); SET TimeZone=EST5EDT; SELECT now(); and so on. Try selecting from your table, too, and you will discover that the time zone of the timestamps changes. If you're used to certain other RDBMSes, this mode of functioning will be unusual, but that really is how it works. Yes had tried this earlier and it works as expected. I think I missed that observation earlier and then was looking to set timezone in postgreSQL.conf which could ultimately resolve this. But better is to set the TimeZone. Now haven't done anything special but JDBC is working with setting TimeZone and ODBC not. So what should I look from here now? Each driver will have its own behavior. For an explanation of the JDBC behavior see here: http://www.postgresql.org/message-id/4b2f2ced.10...@opencloud.com Per Andrews posts, the least surprise behavior is to explicitly set the client time zone. Then you control what is being seen/used. Regards... -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On Thu, Feb 20, 2014 at 3:40 AM, Andrew Sullivan a...@crankycanuck.cawrote: I think your client should set the TimeZone at connection time. That's going to yield the most predictable behaviour for the users, I suspect. Agree! But nothing special is done at JDBC level, is the JDBC driver setting things like TimeZone by default? How to do same in case of ODBC? Regards...
Re: [GENERAL] Timezone information
Or, if you don't mind a little patching: http://pastebin.com/5AyaX2RF That restores the pre-9.1 functionality of determining the timezone on postmaster start. As has been pointed out, their new stuff is more useful if you're shipping instances to the farthest reaches of the planet (We've only got clients in North America at the moment). You will undoubtedly need to make some modifications to patch with 9.3, but I've got hundreds of 9.2 instances in the field now that, well, just work. *Terence J. Ferraro* On Wed, Feb 19, 2014 at 5:10 PM, Andrew Sullivan a...@crankycanuck.cawrote: On Thu, Feb 20, 2014 at 03:37:20AM +0530, Dev Kumkar wrote: But better is to set the TimeZone. Now haven't done anything special but JDBC is working with setting TimeZone and ODBC not. So what should I look from here now? I think your client should set the TimeZone at connection time. That's going to yield the most predictable behaviour for the users, I suspect. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
Dev Kumkar devdas.kum...@gmail.com writes: On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver adrian.kla...@aklaver.comwrote: Do you know where the machine is going when you do the install? No if that was the case then target could have set before shipping itself. Machine TGT-Region is not known here. The functionality of determining an IANA timezone name equivalent to the platform's behavior is currently embedded in initdb and isn't separately accessible. So you've got several options: 1. Wait to do initdb until the machine is installed. 2. Modify the timezone setting in postgresql.conf after the machine is installed (although there might not be an easy way to determine what to set it to). 3. Leave the server timezone setting as GMT and rely on clients to select the zone they want to work in. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone information
On 02/19/2014 01:52 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan a...@crankycanuck.ca mailto:a...@crankycanuck.ca wrote: No, select now() would return the time in whatever timezone is set, or the timezone that the server defaulted to if there's nothing set by the client. So in your installation, set up the server to use UTC by default and, if you like, set the client's time zone according to locale or whatever when the client connects. In my case the timezone parameter gets set. If I comment/remove it then UTC is returned by default. So server here defaulted to UTC. Executed Select now() from pgAdmin and psql, time gets returned in UTC. The timestamps in the server are not actually in a time zone. They're all stored as UTC, and the display is altered according to what your time zone settings are at the time of query. Hmm. Missed one observation here, created a test table with timestamp column of type 'default current_timestamp'. When the query is executed from JDBC then it stores OS specific local time into this column. It depends on how you are declaring the timestamp field. If you do not use with time zone then the input value is open to interpretation and is not 'anchored' to a point in time. Example My time zone is currently PST. test= create table timestamp_test(id int, ts timestamp, ts_z timestamp with time zone); CREATE TABLE test= insert into timestamp_test values (1, now(), now()); INSERT 0 1 test= select * from timestamp_test ; id |ts | ts_z +---+-- 1 | 2014-02-19 14:37:17.53107 | 2014-02-19 14:37:17.53107-08 (1 row) test= select ts at time zone 'UTC', ts_z at time zone 'UTC' from timestamp_test; timezone | timezone --+--- 2014-02-19 06:37:17.53107-08 | 2014-02-19 22:37:17.53107 (1 row) If you know what time zone the value was inserted under you can get it back. test= insert into timestamp_test values (1, now(), now()); INSERT 0 1 test= select * from timestamp_test ; id | ts | ts_z ++--- 1 | 2014-02-19 14:57:35.418363 | 2014-02-19 14:57:35.418363-08 (1 row) test= select ts at time zone 'PST', ts_z at time zone 'PST' from timestamp_test; timezone| timezone ---+ 2014-02-19 14:57:35.418363-08 | 2014-02-19 14:57:35.418363 (1 row) That assumes a lot, so the best thing is to use timestamp with time zone. However when the same query is executed from ODBC then it behaves either as per the timezone set in postgreSQL.conf or when not set then UTC. So looks like am missing some setting while executing query from ODBC. Btw also the pgAdmin and psql behave same as ODBC case. What am missing here which JDBC is doing correctly. Regards... -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
