Re: Date time problem with timstamp with no timezone data
От | Yuva Chandolu |
---|---|
Тема | Re: Date time problem with timstamp with no timezone data |
Дата | |
Msg-id | A0F24737FCB34F489EC955D143BDD851BF8508@exchange-sf1.corp.ebates.com обсуждение исходный текст |
Список | pgsql-jdbc |
Hi Barry, Here is the code(jsp) we wrote ... ============================================================================ == <%@page language = "java" import = " java.util.*, java.sql.*, java.math.*, com.ebates.*, com.ebates.db.*, com.ebates.domain.*; " %> <% DatabaseAdapter dba = null; Test1 temp = null; String query_select = "select test1_id, test1_date from test1 where test1_id=?"; String query_insert = "insert into test1 (test1_id, test1_date) values(nextval('test1_seq'), ?)"; String query_update = "update test1 set test1_date=? where test1_id=?"; String query_id = "select currval('test1_seq')"; try { dba = new DatabaseAdapter("\\dbmove\\test1.jsp"); Timestamp ts = new Timestamp(System.currentTimeMillis()); Connection conn = dba.openConnection(); PreparedStatement pstmt_select = conn.prepareStatement(query_select); PreparedStatement pstmt_insert = conn.prepareStatement(query_insert); PreparedStatement pstmt_update = conn.prepareStatement(query_update); Statement stmt_id = conn.createStatement(); ResultSet rs = null; Timestamp date = null; int id = -1; /************************DIRECT CONN*************************************/ //insert, select System.out.println("\n\n\n*************START************"); System.out.println("insert, select"); System.out.println("dbmove/test1.jsp: before insert date = \t"+ts); pstmt_insert.clearParameters(); pstmt_insert.setTimestamp(1, ts); pstmt_insert.executeUpdate(); rs = stmt_id.executeQuery(query_id); if(rs != null && rs.next()) { id = rs.getInt(1); } rs.close(); pstmt_select.clearParameters(); pstmt_select.setInt(1, id); rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp: after insert date = \t"+date); } rs.close(); System.out.println("insert, select\n"); //insert, select, update(no modification), select, update(no modification), select System.out.println("insert, select, update(no mod), select, update(no mod), select"); System.out.println("dbmove/test1.jsp: before insert date = \t"+ts); //insert pstmt_insert.clearParameters(); pstmt_insert.setTimestamp(1, ts); pstmt_insert.executeUpdate(); rs = stmt_id.executeQuery(query_id); if(rs != null && rs.next()) { id = rs.getInt(1); } rs.close(); //select, update(no modification) pstmt_select.clearParameters(); pstmt_select.setInt(1, id); rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp: after insert date = \t"+date); } rs.close(); System.out.println("dbmove/test1.jsp:before update1 date = \t"+date); pstmt_update.clearParameters(); pstmt_update.setTimestamp(1, date); pstmt_update.setInt(2, id); pstmt_update.executeUpdate(); //select, update(no modification) pstmt_select.clearParameters(); pstmt_select.setInt(1, id); rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp:after update1 date = \t"+date); } rs.close(); System.out.println("dbmove/test1.jsp:before update2 date = \t"+date); pstmt_update.clearParameters(); pstmt_update.setTimestamp(1, date); pstmt_update.setInt(2, id); pstmt_update.executeUpdate(); //select rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp:after update2 date = \t"+date); } rs.close(); System.out.println("insert, select, update(no mod), select, update(no mod), select\n"); //insert, select, update(with modification), select, update(with modification), select System.out.println("insert, select, update(with mod), select, update(with mod), select"); System.out.println("dbmove/test1.jsp: before insert date = \t"+ts); //insert pstmt_insert.clearParameters(); pstmt_insert.setTimestamp(1, ts); pstmt_insert.executeUpdate(); rs = stmt_id.executeQuery(query_id); if(rs != null && rs.next()) { id = rs.getInt(1); } rs.close(); //select, update(with modification) pstmt_select.clearParameters(); pstmt_select.setInt(1, id); rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp: after insert date = \t"+date); } rs.close(); date = new Timestamp(System.currentTimeMillis()); System.out.println("dbmove/test1.jsp:before update1 date = \t"+date); pstmt_update.clearParameters(); pstmt_update.setTimestamp(1, date); pstmt_update.setInt(2, id); pstmt_update.executeUpdate(); //select, update(with modification) pstmt_select.clearParameters(); pstmt_select.setInt(1, id); rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp:after update1 date = \t"+date); } rs.close(); date = new Timestamp(System.currentTimeMillis()); System.out.println("dbmove/test1.jsp:before update2 date = \t"+date); pstmt_update.clearParameters(); pstmt_update.setTimestamp(1, date); pstmt_update.setInt(2, id); pstmt_update.executeUpdate(); //select rs= pstmt_select.executeQuery(); if(rs != null && rs.next()) { date = rs.getTimestamp(2); System.out.println("dbmove/test1.jsp:after update2 date = \t"+date); } rs.close(); System.out.println("insert, select, update(with mod), select, update(with mod), select"); System.out.println("*************END************\n\n\n"); } finally { if(dba != null) { dba.closeConnection(); } } %> ============================================================================ == -----Original Message----- From: Barry Lind [mailto:barry@xythos.com] Sent: Thursday, July 11, 2002 11:02 AM To: Yuva Chandolu Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Date time problem with timstamp with no timezone data type Yuva, There isn't enough information here to really understand what is going on. For example are you doing the update via sql (i.e. update foo set bar = bar + '7 hours'::interval) or are you doing the change in java (i.e. update foo set bar = ? where the bind value is calculated in java). A simple test case showing the problem would help us better diagnose your issue. thanks, --Barry Yuva Chandolu wrote: >Hi, > >We have weiered problem with one timestamp(without timezone) data type >column(updated_date) in one table. We insert a row into this table with >current time once and susequently update it many times with current time. >What happens is with each update the updated_date is increased by 7 hours. >We are in PDT timezone and for initial insert +7 hours is fine but >subsequent updates increasing it by +7 hours is wiered. What should we do >here? > >We are using stable pgjdbc2.jar and we are using postgres7.2.1. > >Thanks >Yuva > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > >
В списке pgsql-jdbc по дате отправления:
Следующее
От: "David A. Bartmess"Дата:
Сообщение: Re: Date time problem with timstamp with no timezone data