Solved

JDBC setTimestamp setting to yesterday's date

Posted on 2002-05-01
23
257 Views
Last Modified: 2010-03-31
I have the following in a DAO class:

pstmt.setTimestamp(13, SQLUtil.getNullSafe(info.getLastLogin()));

SQLUtil.getNullSafe is returning:

2001-05-01 00:00:00.0

But my query gets the following value inserted:

2002-04-30 23:00:00

Is there a bug in PrepareStatement.setTimestamp?  I'm sending in the correct value?

I'm using jdk 1.3.1_01

Thanks,

Matt
0
Comment
Question by:mraible
  • 12
  • 8
  • 2
  • +1
23 Comments
 
LVL 92

Expert Comment

by:objects
ID: 6984488
Are the timezones identical for both queries?
0
 
LVL 1

Author Comment

by:mraible
ID: 6984831
Yes, timezones are the same, client and server are on same machine, also this all takes place on the server.

I'm printing out this value:

SQLUtil.getNullSafe(info.getLastLogin())

And them I'm printing out the pstmt value, and it's different in the query??  Wierd - is this "As Designed?"

0
 
LVL 92

Expert Comment

by:objects
ID: 6984838
I'm not sure how the date is actually stored in the database, but it would be interesting to see exactly what is there. And compare this to the actual value passed from Java:

info.getLastLogin().getTime();

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:mraible
ID: 6984844
Here is the code from my update statement.  logCat.debug is log4j, similar to System.out.println.  You can see that logCat.debug(info) prints out a value of May 1st for last_update, but the preparedStatement contains 4-30-02?

     logCat.debug(info);
        try {
            String sQuery = "UPDATE user SET user_first_name=?, user_last_name=?, user_title=?, user_organization=?, user_address=?, user_email=?, user_password=?, user_timezone=?, create_date=?, create_user_id=?, update_date=?, update_user_id=?, last_login=? WHERE user_id=?";
            pstmt = con.prepareStatement(sQuery);

            pstmt.setString(1, info.getFirstName());
            pstmt.setString(2, info.getLastName());
            pstmt.setString(3, info.getTitle());
            pstmt.setString(4, info.getOrganization());
            pstmt.setString(5, info.getAddress());
            pstmt.setString(6, info.getEmail());
            pstmt.setString(7, info.getPassword());
            pstmt.setInt(8, SQLUtil.getNullSafe(info.getTimezone()));
            pstmt.setTimestamp(9, SQLUtil.getNullSafe(info.getCreateDate()));
            pstmt.setInt(10, SQLUtil.getNullSafe(info.getCreateUserId()));
            pstmt.setTimestamp(11, SQLUtil.getNullSafe(info.getUpdateDate()));
            pstmt.setInt(12, SQLUtil.getNullSafe(info.getUpdateUserId()));
               pstmt.setTimestamp(13, SQLUtil.getNullSafe(info.getLastLogin()));
            pstmt.setInt(14, info.getPK().getUserId());
            logCat.debug(pstmt);
            pstmt.execute();

And here is the printout from that:

DEBUG [HttpProcessor[80][4]] [Persistence] UserDAOMySQL.update(194) |
com.onpoint.webapp.persistence.UserVO
        pk=com.onpoint.webapp.persistence.UserPK
        userId=1001

        firstName=Alice
        lastName=Smith
        title=Project Manager
        organization=Acme, Inc.
        address=563 6th Ave
Missoula, MT 59801
        email=user@acme.com
        password=12dea96fec20593566ab75692c9949596833adc9
        timezone=-7
        createDate=Wed Apr 10 00:00:00 MDT 2002
        createUserId=1
        updateDate=Wed Apr 10 00:00:00 MDT 2002
        updateUserId=1001
        lastLogin=Wed May 01 00:00:00 MDT 2002

DEBUG [HttpProcessor[80][4]] [Persistence] UserDAOMySQL.update(213) | org.gjt.mm.mysql.jdbc2.Prepare
dStatement@15a9e: UPDATE user SET user_first_name='Alice', user_last_name='Smith', user_title='Proje
ct Manager', user_organization='Acme, Inc.', user_address='563 6th Ave
Missoula, MT 59801', user_email='user@acme.com', user_password='12dea96fec20593566ab75692c9949596833
adc9', user_timezone=-7, create_date='2002-04-09 23:00:00', create_user_id=1, update_date='2002-04-0
9 23:00:00', update_user_id=1001, last_login='2002-04-30 23:00:00' WHERE user_id=1001[B@645a5c
DEBUG [HttpProcessor[80][4]] [Services] UserDelegate.saveUser(221) | Successfully updated user recor
d in database!
0
 
LVL 92

Expert Comment

by:objects
ID: 6984852
The value that is printed out is just a string representation of the date.
I'd be interested to seeing the actual date values as mentioned above.

0
 
LVL 1

Author Comment

by:mraible
ID: 6984881
info.getLastLogin().getTime(): 1020232800000
0
 
LVL 1

Author Comment

by:mraible
ID: 7128309
Please delete as an answer was never found and I ended up writing a workaround.
0
 
LVL 92

Expert Comment

by:objects
ID: 7128321
You never reported what was actually stored in the database.
0
 
LVL 1

Author Comment

by:mraible
ID: 7128408
2002-04-30 23:00:00

It's in my post above at:

Date: 05/01/2002 04:57PM PST
0
 
LVL 92

Expert Comment

by:objects
ID: 7128452
Thats the date value in the Java program.
Whats the value actually stored in the database.
0
 
LVL 1

Author Comment

by:mraible
ID: 7128475
This is the problem, please read the original post.

My Java program has:

lastLogin=Wed May 01 00:00:00 MDT 2002

When I do:

pstmt.setTimestamp(13, SQLUtil.getNullSafe(info.getLastLogin()));

It becomes:

'2002-04-30 23:00:00'

My SQLUtil.getNullSafe only returns the a current timestamp if the info.getLastLogin is null.
0
 
LVL 92

Expert Comment

by:objects
ID: 7128553
Can you post the code for:

SQLUtil.getNullSafe
0
 
LVL 1

Author Comment

by:mraible
ID: 7128720
  /**
    * Convenience method to convert Date nulls to a Timestamp
    *
    * @param aDate - Date to check for null
    */
    public static Timestamp getNullSafe(Date aDate) {
        Timestamp ts = null;
        if (aDate != null) {
            // fix bug in preparedStatement by adding an hour
            ts = new Timestamp(aDate.getTime() + 60 * 60 * 1000);
        } else {
            // return current time
            ts = new Timestamp(new Date().getTime());
        }
        return ts;
    }
0
 
LVL 92

Expert Comment

by:objects
ID: 7128787
The Timestamp class uses GMT time, while the Date class uses the default timezone. I think that is why you are seeing a difference.

0
 
LVL 92

Expert Comment

by:objects
ID: 7128850
Scratch that, but there are some differences that may cause your problem which I'm investigating. I don't think it has anything to do with setTimestamp, I think the difference is caused by the Timestamp class itself.
0
 
LVL 92

Expert Comment

by:objects
ID: 7128866
What version of Java are you running?
0
 
LVL 92

Expert Comment

by:objects
ID: 7128899
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7992877
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Answered by: objects
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Venci75
EE Cleanup Volunteer
0
 
LVL 1

Author Comment

by:mraible
ID: 7995769
This turned out to be a bug in the MySQL JDBC Driver.  Release 2.0.14 fixed it.  
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8048373
Bug mentioned by objects, however the link provided requires logon so I cannot tell if it is the correct bug.  PAQing with refund of the 50 points.  

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 92

Expert Comment

by:objects
ID: 8048945
> however the link provided requires logon so I cannot
> tell if it is the correct bug.  

So how would you suggest I post details of bug reports then?
0
 

Expert Comment

by:SpideyMod
ID: 8049007
objects,
If you can assure me that the bug report in the link you provided is indeed the same bug spoken of by mraible's last comment, your word is good enough for me and I will issue a "points for objects" question.  I don't subscribe to these types of sites because they tend to generate an awful lot of email and membership list sellings.  In the future, you can always copy and paste the bug report from the web-site or the relevant details.
0
 
LVL 92

Expert Comment

by:objects
ID: 8049088
Hard to say for sure without more info so I'm happy with the current state of the question.
My query was more in general terms. Copy & pasting the page is not really viable due to the layout of the bug report page.
Though I've provided a lot of similiar links in the paste and you were the first not willing to register (that I've known about) so I might just continue posting them.

:)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
custom annotations 9 42
why cannot we forward request once the response is committed 2 46
table example 4 32
Java basic valueOf question 1 29
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question