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 ...
>
>
>============================================================================
>==
><%@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
>>
>>
>>
>>
>>
>
>
>
>