JDBC and Timestamps, something seems to be broken for me.

bdzevel

Hello,

First, my code:

//------------------------ GET FIRST TIMESTAMP VALUE:

        String sqlstr = "SELECT last_start_date FROM user_scheduler_jobs WHERE upper(job_name) = upper(?)";
        PreparedStatement prest = c.prepareStatement(sqlstr);
        prest.setString(1, jobName);
        ResultSet rs = prest.executeQuery();
        Timestamp startTime = new Timestamp(0);
        if (rs.next()) {
            startTime = rs.getTimestamp(1, Calendar.getInstance(TimeZone.getTimeZone("UTC")));
            System.out.println("START_TIMESTAMP >> " + startTime); //<<<<<<<<<<<<<<<<<<<<<<<
        }

//------------------------ PRINTLN SECOND TIMESTAMP VALUE:

        sqlstr = "SELECT * FROM (SELECT * FROM user_scheduler_job_run_details WHERE upper(job_name) = upper(?) ORDER BY actual_start_date DESC) WHERE rownum = 1";
        prest = c.prepareStatement(sqlstr);
        prest.setString(1, jobName);
        rs = prest.executeQuery();
        if (rs.next())
            System.out.println("REQ_START_DATE >> " + rs.getTimestamp("REQ_START_DATE", Calendar.getInstance(TimeZone.getTimeZone("UTC")))); //<<<<<<<<<<<<<<<<<<<<<

//------------------------ COMPARE THE TWO TIMESTAMPS (IN PL/SQL):

        sqlstr = "SELECT * FROM user_scheduler_job_run_details WHERE upper(job_name) = upper(?) AND req_start_date = ?";
        prest = c.prepareStatement(sqlstr);
        prest.setString(1, jobName);
        prest.setTimestamp(2, startTime, Calendar.getInstance(TimeZone.getTimeZone("UTC")));
        rs = prest.executeQuery();
        if (rs.next())
            System.out.println("SUCCESS"); //<<<<<<<<<<<<<<<<<<<<<<<

I'm sorry for the wall-of-code. The SQL statements don't really matter (as you'll see in a minute), so the things that matter are really the System.out.println() calls that I've pointed out and the final PreparedStatement.

Here is my output:

START_TIMESTAMP >> 2013-01-15 16:44:27.316
REQ_START_DATE >> 2013-01-15 16:44:27.316

Basically, what this code does is it grabs a timestamp from one view and tries to compare the timestamps found in another view to it. Specifically, I'm trying to locate an entry based on timestamp. I used to not have any of those Calendar.getInstance(...) functions in there, but I googled a bit about my issue and everyone seems to suggest to use them to make sure everything is being sent around as the same timezone. In this case, what I did was I pass everything around as UTC.

The output suggests that my startTime (START_TIMESTAMP) is the same as the REQ_START_DATE found in the other view (these are the two values I'm interested in comparing); however, when I run this code and I compare the two timestamps in a PreparedStatement, it doesn't seem to find any rows (although they clearly exist, as per the PreparedStatement that is executed JUST before that one).

As you can see in my output, there is no "SUCCESS" logged, meaning it doesn't find any rows where 'req_start_date = ?'.

So, I don't get it... if the two timestamps come back identical, then why is the third statement that compares them failing to find anything?

Thanks.

Unknown

Starting with the last question since that is the main clue.
>
So, I don't get it... if the two timestamps come back identical, then why is the third statement that compares them failing to find anything?
>
The only valid conclusions you can draw from that statement are:

1. the two timestamps DID NOT 'come back identical - although you may have displayed them using a precision that makes them 'appear' to be identical. The values 1.25 and 1.25 'appear' to be identical when displayed with two decimal positions but when displayed with three or more might actually be '1.246' and 1.254'; the actual precision is lost due to the default rounding that occurs when the data is displayed using two decimals.

2. the third statement isn't comparing them properly. Using the same example from #1. If the view has 1.246 and you extract and save it as 1.25 and then use 1.25 to try to find 1.246 in the view it won't match.

3. the third statement isn't comparing the first two at all. You may have been fooled by your own assumption stated in this line: COMPARE THE TWO TIMESTAMPS (IN PL/SQL):
That doesn't compare anything in PL/SQL. It compares one value in Oracle with one value from Java; and the Java value likely no longer matches the original Oracle value it was sourced from.
>
Basically, what this code does is it grabs a timestamp from one view and tries to compare the timestamps found in another view to it. Specifically, I'm trying to locate an entry based on timestamp. I used to not have any of those Calendar.getInstance(...) functions in there, but I googled a bit about my issue and everyone seems to suggest to use them to make sure everything is being sent around as the same timezone. In this case, what I did was I pass everything around as UTC.

The output suggests that my startTime (START_TIMESTAMP) is the same as the REQ_START_DATE found in the other view (these are the two values I'm interested in comparing); however, when I run this code and I compare the two timestamps in a PreparedStatement, it doesn't seem to find any rows (although they clearly exist, as per the PreparedStatement that is executed JUST before that one).

As you can see in my output, there is no "SUCCESS" logged, meaning it doesn't find any rows where 'req_start_date = ?'.
>
See the getTimestamp method in the Javadocs
http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getTimestamp(int, java.util.Calendar)
>
getTimestamp
Timestamp getTimestamp(int columnIndex,
Calendar cal)
throws SQLExceptionRetrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language. This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.
>
To compare the values in the DB modify the query to get the value from the first query that you used rather than pull the value into Java and then send it back again.

 SELECT * FROM user_scheduler_job_run_details 
 WHERE upper(job_name) = upper(?) 
 AND req_start_date = (
    SELECT last_start_date 
    FROM user_scheduler_jobs 
    WHERE upper(job_name) = upper(?))

That nested sub-query is the same as the first query you posted. That gets the 'last_start_date' from the DB rather than pull it into Java.

PhHein

You should try to get the statements identical, i.e
second stmt:

sqlstr = "SELECT REQ_START_DATE FROM user_scheduler_job_run_details WHERE upper(job_name) = upper(?)  AND rownum = 1 ORDER BY actual_start_date DESC";
....
Timestamp reqStartTime = new Timestamp(0);
if (rs.next()) {
   reqStartTime = rs.getTimestamp(1, Calendar.getInstance(TimeZone.getTimeZone("UTC")));
   System.out.println("REQ_START_DATE >> " + reqStartTime);
}

next compare the two timestamps with

System.out.println(startTime.getTime());
System.out.println(reqStartTime.getTime());

If they are still identical, get rid of the Calendar when reading the resultSet and try again.
EDIT: we also have a JDBC forum, that had been a better place for your thread.

Edited by: PhHein on 16.01.2013 09:16

bdzevel

That select statement that you posted doesn't yield the correct results (in other words, I get the wrong row back). I'm not really a SQL person, and I didn't originally write this code, but it does the job. I wish I could make this easier to look at, but I'm not sure of a quick way to do that.

I already println'd both timestamps. As you can see in my output, both timestamps are identical; however, when I compare them in a sql statement, for whatever reason it returns no rows.

Also, as I've mentioned I just recently (yesterday) put the "Calendar stuff" in there as I was debugging this issue. I never had this in there beforehand (and it still wasn't working).

output with ".getTime()"

START_TIMESTAMP >> 1358286267316
REQ_START_DATE >> 1358286267316

(Identical, still no "SUCCESS" logged)

output without "Calendar.getInstance(...)"

START_TIMESTAMP >> 2013-01-15 17:44:27.316
REQ_START_DATE >> 2013-01-15 17:44:27.316

(Also identical, though different time... still no "SUCCESS" logged)

Also, thanks for moving the thread to a better location.

Edited by: 964530 on Jan 16, 2013 6:27 AM

PhHein

Then I have no more ideas, sorry.

PS: I didn't move the thread.

805574

Noting that the two values are coming from two different tables.

Make sure in your debugging you are also comparing the nanos values as well, as those are used in equality comparisons. Depending on how the data in the tables is populated, it's possible that those values are different causing your problems.

Don't see anything else that could be affecting your results.

gimbal2

Assuming Oracle DBMS here, given the table names. Its actually pretty useful information to give up front you know.

I'm wondering what you're seeing on the Oracle side of things, as all output I see here is coming after it has gone through the Java mill. When you look at the data with for example pl/sql developer, what do you see then? The same timestamps or are they different?

On top of that: what happens when you just run the query in pl/sql dev or sqlplus? Something like "fetch me all stuff from table B where the timestamp is that of table A". Do you get results then? It isn't going to work on the Java side of things when you can't get it to work using a regular SQL client.

Unknown

Starting with the last question since that is the main clue.
>
So, I don't get it... if the two timestamps come back identical, then why is the third statement that compares them failing to find anything?
>
The only valid conclusions you can draw from that statement are:

1. the two timestamps DID NOT 'come back identical - although you may have displayed them using a precision that makes them 'appear' to be identical. The values 1.25 and 1.25 'appear' to be identical when displayed with two decimal positions but when displayed with three or more might actually be '1.246' and 1.254'; the actual precision is lost due to the default rounding that occurs when the data is displayed using two decimals.

2. the third statement isn't comparing them properly. Using the same example from #1. If the view has 1.246 and you extract and save it as 1.25 and then use 1.25 to try to find 1.246 in the view it won't match.

3. the third statement isn't comparing the first two at all. You may have been fooled by your own assumption stated in this line: COMPARE THE TWO TIMESTAMPS (IN PL/SQL):
That doesn't compare anything in PL/SQL. It compares one value in Oracle with one value from Java; and the Java value likely no longer matches the original Oracle value it was sourced from.
>
Basically, what this code does is it grabs a timestamp from one view and tries to compare the timestamps found in another view to it. Specifically, I'm trying to locate an entry based on timestamp. I used to not have any of those Calendar.getInstance(...) functions in there, but I googled a bit about my issue and everyone seems to suggest to use them to make sure everything is being sent around as the same timezone. In this case, what I did was I pass everything around as UTC.

The output suggests that my startTime (START_TIMESTAMP) is the same as the REQ_START_DATE found in the other view (these are the two values I'm interested in comparing); however, when I run this code and I compare the two timestamps in a PreparedStatement, it doesn't seem to find any rows (although they clearly exist, as per the PreparedStatement that is executed JUST before that one).

As you can see in my output, there is no "SUCCESS" logged, meaning it doesn't find any rows where 'req_start_date = ?'.
>
See the getTimestamp method in the Javadocs
http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#getTimestamp(int, java.util.Calendar)
>
getTimestamp
Timestamp getTimestamp(int columnIndex,
Calendar cal)
throws SQLExceptionRetrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language. This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.
>
To compare the values in the DB modify the query to get the value from the first query that you used rather than pull the value into Java and then send it back again.

 SELECT * FROM user_scheduler_job_run_details 
 WHERE upper(job_name) = upper(?) 
 AND req_start_date = (
    SELECT last_start_date 
    FROM user_scheduler_jobs 
    WHERE upper(job_name) = upper(?))

That nested sub-query is the same as the first query you posted. That gets the 'last_start_date' from the DB rather than pull it into Java.

bdzevel

I just ran the below code in PL/SQL Dev. I get a row back.

SELECT * FROM user_scheduler_job_run_details 
 WHERE upper(job_name) = upper('QA_JOB_RTM9') 
 AND req_start_date = (
    SELECT last_start_date 
    FROM user_scheduler_jobs 
    WHERE upper(job_name) = upper('QA_JOB_RTM9'));

I'll try to use this line instead of the shitty code I have in there and maybe it'll work. I'll update with status.

Thanks for all the help, you guys.

EDIT: This statement worked in Java. I'm going to switch the hodgepodge of code that I currently have to use this statement instead.

Thank you so much. I guess the problem was a loss of precision when going from JDBC to java and back.

Edited by: 964530 on Jan 16, 2013 11:51 AM

jschellSomeoneStoleMyAlias

Generally it is not going to be a good idea to attempt an exact match on timestamps when doing queries in databases unless the timestamp value has been normalized both in the query and in the data (inserted normalized.)

This is due to precision differences within the database as well as within java.

Normalization should be no more precise than seconds. (And myself I would test even that extensively.)

Unless it is normalized one should use a range query instead using exclusion at one end and inclusion at the other.