Solved

JDBC setTimestamp setting to yesterday's date

Posted on 2002-05-01
23
256 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

815 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

10 Experts available now in Live!

Get 1:1 Help Now