Re: Timezone information

On 02/19/2014 02:07 PM, Dev Kumkar wrote:
> On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca
> <mailto:ajs(at)crankycanuck(dot)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(dot)klaver(at)aklaver(dot)com