Timezone information

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]http://www.iana.org/time-zones, 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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan <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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan <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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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...

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 <ajs@crankycanuck.ca>wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general