Re: Date time problem with timstamp with no timezone data

Yuva,

I know that you have decided to use timestamp with time zone instead of
timestamp without time zone, but I wanted to let you know that the bug
with support for timestamp without time zone is now fixed in the latest
development build on jdbc.postgresql.org.

thanks,
--Barry

Yuva Chandolu wrote:

>Hi Barry,
>
>Here is the code(jsp) we wrote ...
>
>
>============================================================================
>==
><%(at)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(at)xythos(dot)com]
>Sent: Thursday, July 11, 2002 11:02 AM
>To: Yuva Chandolu
>Cc: pgsql-jdbc(at)postgresql(dot)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
>>
>>
>>
>>
>>
>
>
>
>