Solved

JDBC setTimestamp setting to yesterday's date

Posted on 2002-05-01
23
249 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
Comment Utility
Are the timezones identical for both queries?
0
 
LVL 1

Author Comment

by:mraible
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:mraible
Comment Utility
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
Comment Utility
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
Comment Utility
info.getLastLogin().getTime(): 1020232800000
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

by:mraible
Comment Utility
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
Comment Utility
Thats the date value in the Java program.
Whats the value actually stored in the database.
0
 
LVL 1

Author Comment

by:mraible
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 92

Expert Comment

by:objects
Comment Utility
Can you post the code for:

SQLUtil.getNullSafe
0
 
LVL 1

Author Comment

by:mraible
Comment Utility
  /**
    * 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
Comment Utility
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
Comment Utility
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
Comment Utility
What version of Java are you running?
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 
LVL 9

Expert Comment

by:Venci75
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now