On 02/19/2014 01:52 PM, Dev Kumkar wrote:
On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan <ajs@xxxxxxxxxxxxxxx
<mailto:ajs@xxxxxxxxxxxxxxx>> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
