Обсуждение: Timezone information
How to set timezone in postgreSQL database to pick operating system level timezone information.
However this value needs to be in format 'US/Pacific', 'Asia/Calcutta' and so on to work correctly.
Took a look into pg_timezone_names table and
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation
has TimeZoneKeyName set as "India Standard Time". However post installation postgresql.conf has timezone information set as 'Asia/Calcutta', is there any binary which can be executed?
Regards...
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }When postgres is installed on IST timezone windows operating system, registry keyHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation
has TimeZoneKeyName set as "India Standard Time". However post installation postgresql.conf has timezone information set as 'Asia/Calcutta', is there any binary which can be executed?
Regards...
Dev Kumkar <devdas.kumkar@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
On Wed, Feb 19, 2014 at 8:31 PM, Tom Lane <tgl@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.
At the time of postgreSQL install how does it pick timezone information and sets into postgreSQL.conf accordingly.
Regards...
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
On Thu, Feb 20, 2014 at 2:06 AM, John R Pierce <pierce@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...
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 ajs@crankycanuck.ca
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 >
On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan <ajs@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.
Regards...
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 ajs@crankycanuck.ca
On 02/19/2014 01:16 PM, Dev Kumkar wrote: > On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan <ajs@crankycanuck.ca > <mailto:ajs@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.klaver@aklaver.com
On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
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.
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.
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...
On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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...
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 ajs@crankycanuck.ca
On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
I think I missed that observation earlier and then was looking to set timezone in postgreSQL.conf which could ultimately resolve this.
On Thu, Feb 20, 2014 at 03:22:15AM +0530, Dev Kumkar wrote:Probably the JDBC driver is setting its TimeZone. Really, try it:
>
> 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.
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...
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 ajs@crankycanuck.ca
On Thu, Feb 20, 2014 at 3:40 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
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...
On 02/19/2014 02:07 PM, Dev Kumkar wrote: > On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan <ajs@crankycanuck.ca > <mailto:ajs@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.10400@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.klaver@aklaver.com
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 <ajs@crankycanuck.ca> wrote:
On Thu, Feb 20, 2014 at 03:37:20AM +0530, Dev Kumkar wrote:I think your client should set the TimeZone at connection time.
> 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?
That's going to yield the most predictable behaviour for the users, I suspect.
A
--
Andrew Sullivan
ajs@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
Dev Kumkar <devdas.kumkar@gmail.com> writes:
> On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver <adrian.klaver@aklaver.com>wrote:
>> 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
On 02/19/2014 01:52 PM, Dev Kumkar wrote:
> On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan <ajs@crankycanuck.ca
> <mailto:ajs@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.klaver@aklaver.com
On Thu, Feb 20, 2014 at 3:53 AM, Adrian Klaver <adrian.klaver@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
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...
On Thu, Feb 20, 2014 at 3:55 AM, Terence Ferraro <terencejferraro@gmail.com> wrote:
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.
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...
inline:
On Thu, Feb 20, 2014 at 3:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
No doubt this is solution and doable but doesn't fit into my current deployment model.
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.
JDBC there is no issue. However if there is any clue at ODBC driver level, please let know.
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...
On Thu, Feb 20, 2014 at 4:31 AM, Adrian Klaver <adrian.klaver@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.
Regards...
On 02/20/2014 04:29 AM, Dev Kumkar wrote: > On Thu, Feb 20, 2014 at 3:53 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.10400@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.klaver@aklaver.com
On 02/20/2014 04:59 AM, Dev Kumkar wrote: > On Thu, Feb 20, 2014 at 4:31 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com
On Thu, Feb 20, 2014 at 9:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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...
Regards...
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 ajs@crankycanuck.ca
On Fri, Feb 21, 2014 at 12:43 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
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...