Re: Date time problem with timstamp with no timezone data

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