Timestamp Problem

hernann · September 20th, 2007, 08:31 AM

Hello all, how r u ? wishing all the best of luck.
I have a problem with Timestamp.
this is the problem :
I have a DB Oracle 10g, and i made a query to return fields from the DB ( fields were : date1 as Date & load as Number) and the rows returned with some parameters are :
date1 load
DD/MM/YYYY
1 - 01/05/2007 0.0
2 - 02/05/2007 2.6
3 - 03/05/2007 8.3
4 - 04/05/2007 10.9

when inserting this statement in java code and return rows in ResultSet, I made 2 objects : Timestamp/Date for date1 and BigDecimal for load and the result is:
1 - 01/05/2007 00:00:00 0.0 // it is OK
2 - 02/05/2007 00:00:00 2.6 // it is OK
3 - 02/05/2007 23:00:00 8.3 // ERROR
4 - 04/05/2007 00:00:00 10.9 ( not 03/05/2007 )

PLZ, if any one can help me in this problem

and thanks alot.

hernann · September 23rd, 2007, 02:16 AM

Why NO Reply ???

iamrakesh22 · September 23rd, 2007, 11:18 PM

try using java.sql.Date, like resultSetObject.getDate(1) and use it. If you've posted little code it'd been little helpful.

- Rakesh

hernann · September 24th, 2007, 01:00 AM

when i select a date and a number from Datebase in TOAD for Oracle Application as
select date1, load from c_day
the result is :
date1 ( type Date ) load ( type Number)
DD/MM/YYYY
01/05/2007 20.30
02/05/2007 16.21
03/05/2007 65.014
04/05/2007 20.30

when inserting this query in Java Code as:
Timestamp t = null;
BigDecimal b = null;
String sql = "select date1, load from c_day";
PreparedStatement stm = con.prepareStatement(sql);
ResultSet rs = stm.executeQuery();
while(rs.next){
t = rs.getTimestamp(1);
b = rs.getBigDecimal(2);
System.out.println("date : " + t.toString() + " , load = " + b.doubleValue());
}
// some dates is wrong .
Result
date : 01/05/2007 00:00:00 , load = 20.30
date : 02/05/2007 00:00:00 , load = 16.21
date : 02/05/2007 23:00:00 , load = 65.014
date : 04/05/2007 00:00:00 , load = 20.30

iamrakesh22 · September 26th, 2007, 07:09 AM

Check the article on Date data type at the following link
http://www.tar.hu/sqlbible/sqlbible0023.html

According to that article Date type also holds time information. May be the date1 column has that time information stored for row 3 in your table. That is why the time is been given as 23:00:00.
If you are using date1 column to store only date the change the java code like

Code:

t = rs.getDate(1); instead of using t = rs.getTimestamp(1);

- Rakesh

hernann · September 26th, 2007, 07:51 AM

when using rs.getDate(1) , it returned the following :
date1 = 01/05/2007 , load ...
date1 = 02/05/2007 , load ...
date1 = 02/05/2007 , load ...
date1 = 04/05/2007 , load ...