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.