Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

JDBC setTimestamp setting to yesterday's date

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
mraible
Asked:
mraible
  • 12
  • 8
  • 2
  • +1
1 Solution
 
objectsCommented:
Are the timezones identical for both queries?
0
 
mraibleAuthor Commented:
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
 
objectsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mraibleAuthor Commented:
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
 
objectsCommented:
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
 
mraibleAuthor Commented:
info.getLastLogin().getTime(): 1020232800000
0
 
mraibleAuthor Commented:
Please delete as an answer was never found and I ended up writing a workaround.
0
 
objectsCommented:
You never reported what was actually stored in the database.
0
 
mraibleAuthor Commented:
2002-04-30 23:00:00

It's in my post above at:

Date: 05/01/2002 04:57PM PST
0
 
objectsCommented:
Thats the date value in the Java program.
Whats the value actually stored in the database.
0
 
mraibleAuthor Commented:
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
 
objectsCommented:
Can you post the code for:

SQLUtil.getNullSafe
0
 
mraibleAuthor Commented:
  /**
    * 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
 
objectsCommented:
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
 
objectsCommented:
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
 
objectsCommented:
What version of Java are you running?
0
 
objectsCommented:
0
 
Venci75Commented:
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
 
mraibleAuthor Commented:
This turned out to be a bug in the MySQL JDBC Driver.  Release 2.0.14 fixed it.  
0
 
SpideyModCommented:
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
 
objectsCommented:
> 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
 
SpideyModCommented:
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
 
objectsCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now