Skip site navigation (1) Skip section navigation (2)

Timestamp without Timezone and differing client / server tzs

From: Ken Johanson <pg-user(at)kensystem(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Timestamp without Timezone and differing client / server tzs
Date: 2008-07-08 07:20:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
Hi all ,first let me describe the set up:

Server: Postgresql 8.3.x
Server's postgresql.conf:timezone = UTC
Server's OS (Linux tz): /etc/localtime -> /usr/share/zoneinfo/UTC

Client JDBC driver: PostgreSQL 8.3devel JDBC3g with SSL (build 602)
Client/JVM TZ : America/Denver

Now, execute query:

rs = st.exceuteQuery("SELECT NOW()");//understood to be a with-tz type;
out.println(rs.getString(1)); --> 2008-07-08 07:09:59.284012+00 
(expected, denver +0700)
out.println(rs.getTimestamp(1)); --> 2008-07-08 01:09:59.284012 
(expected, implicit Denver time)

Now do a DML via prepared statement into a Timestamp without Timezone.

ps.setTimestamp(new Timestamp(System.currentTimeMillis()));

and select it:

out.println(rs.getString(1)); --> 2008-07-08 14:09:59.284012+00 
out.println(rs.getTimestamp(1)); --> 2008-07-08 07:09:59.284012 

Question: with ts without tz storage, should not a timestamp be 
normalized to the  servers implicit TZ (UTC in this case), since that is 
set in the config's client-tz?

Also, is it possible/not to set the preferred TZ as a URL param to the 
driver? This would be a safe option (not break apps), I believe. It 
allows the string translation without tz to match the server's implicit 
(zone-less string) tz.



pgsql-jdbc by date

Next:From: Oliver JowettDate: 2008-07-08 07:44:08
Subject: Re: Timestamp without Timezone and differing client / server tzs
Previous:From: Dave CramerDate: 2008-07-07 23:33:15
Subject: Re: server-side prepared Statements

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group