Re: Date time problem with timstamp with no timezone data

Thanks a lot Barry, yes, you are right, we already moved to "timestamptz"
and everything looks fine now. We actually don't need precision upto
milliseconds(datetime datatype was kind of orbitrary choice our DBA people
made looks like). We are fine after moving to timestamptz as of today and
postgres is doing great. any way I will get the latest dev jar and see the
results.

-Yuva Chandolu
Sr. Java Developer
www.ebates.com

-----Original Message-----
From: Barry Lind [mailto:barry@xythos.com]
Sent: Tuesday, July 16, 2002 2:14 PM
To: Yuva Chandolu
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Date time problem with timstamp with no timezone
data type


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
>>
>>
>>
>>
>>
>
>
>
>