Received: from ns.nipsco.com (nss.nipsco.com [199.48.6.11]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA18523 for ; Mon, 17 Apr 2000 13:26:15 -0400 (EDT) (envelope-from pkdhar@nipsco.com) Received: from nsc.nipsco.com (nsc.nipsco.com [199.48.5.11]) by ns.nipsco.com (8.9.0/8.9.0) with ESMTP id MAA28114 for ; Mon, 17 Apr 2000 12:25:34 -0500 (CDT) Received: from fwc.nipsco.com (fwc-dmz.nipsco.com [199.48.5.9]) by nsc.nipsco.com (8.9.0/8.9.0) with SMTP id MAA13171 for ; Mon, 17 Apr 2000 12:25:08 -0500 (CDT) Received: i before e except after c. Message-ID: <38FB48EB.F2DE24CD@nipsco.com> Date: Mon, 17 Apr 2000 12:24:59 -0500 From: Pranab Dhar Organization: NIPSCO INDUSTRIES X-Mailer: Mozilla 4.7 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: TimeStamp Anomaly - Any reasons ? Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/171 Hi PGSQL Users, I have come across a situation where I want to insert a TimeStamp into a table.The TimeStamp is generated by a java program in the format 'YYYY-MM-DD HH:MI:SS.MS' e.g. '2000-04-17 11:41:05.0'. When I tried inserting a time from psql user interface I get a 1 hour difference. I have set TZ=CDT ,PGTZ=CDT.I am running postgres6.5.3 on NT4.0. Here are the steps __________________________________________________________________________________________ testdb=> \d test Table = test +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | updt_dt | timestamp | 4 | +----------------------------------+----------------------------------+-------+ testdb=> insert into test values(current_timestamp); INSERT 24864 1 testdb=> select * from test; updt_dt ---------------------- 2000-04-17 11:41:05-05 (1 row) testdb=> insert into test values('2000-04-17 11:41:05'); INSERT 24865 1 testdb=> select * from test; updt_dt ---------------------- 2000-04-17 11:41:05-05 2000-04-17 12:41:05-05 <== new row (2 rows) testdb=> show time zone testdb-> ; NOTICE: Time zone is CDT SHOW VARIABLE testdb=> ___________________________________________________________________________________________ Now I try to retrieve the same rows from a java program .I get the following result:- D:\>java Table TimeZone:America/Chicago TimeZone:CDT 2000-04-17 11:41:05+01 2000-04-17 12:41:05+01 The code which does this is :- while(rs.next()) { objname = rs.getString("updt_dt"); System.out.println( objname); } If I user this code objname = rs.getTimestamp("updt_dt").toString(); I get this result. 2000-04-17 05:41:05.0 2000-04-17 06:41:05.0 Now If I try to insert '2000-04-17 11:41:05' using the java program st.executeUpdate("insert into test values('2000-04-17 11:41:05')"); the database shows testdb=> select * from test; updt_dt ---------------------- 2000-04-17 11:41:05-05 2000-04-17 12:41:05-05 2000-04-17 06:41:05-05 <== newly inserted row (3 rows) and if I had inserted 2000-04-17 06:41:05.0 it would have been off by 5 hours like this testdb=> select * from test; updt_dt ---------------------- 2000-04-17 11:41:05-05 2000-04-17 12:41:05-05 2000-04-17 06:41:05-05 2000-04-17 01:41:05-05 <== new row (4 rows) I expect a timestamp to be retrieved and stored by a jdbc program without any change as it is.Problem is I lost 5 hours on the timestamp when I try to do that.I wanted the code to be database independent but it looks like there is a timezone conversion involved as the java.sql.Timestamp doesnt allow any timezone information in it. I will appreciate any input on this problem. Thanks PKD