This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes java.sql.Date comparison Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.Date comparison" Watch "java.sql.Date comparison" New topic
Author

java.sql.Date comparison

Paulo Salgado
Ranch Hand

Joined: Jan 18, 2002
Posts: 98
Hello All.
Something is bugging me here and I felt like writing in this forum so anybody may shed a light.
I'm using java.sql.Date objects to store values in DATE columns in the database. Everything OK, the date is stored as expected.
The problem arises when I need to do comparisons.
For instance, if I create a new java.sql.Date (with today's date) and then retrieve a DATE column (with today's date) into another java.sql.Date, I get false as result of the equals() method.
I was looking the reason for that and concluded it lies on the fact that java.sql.Date.equals() is in fact inherited from java.util.Date, and in that class it compares both the Date and the Time for equality. When the DATE column value is retrieved from the database, of course, it has no time value.
Here is the java.util.Date.equals() method:

I'm assuming java.sql.Date is to be used to handle DATE columns and with that thought in mind wouldn't it make sense for java.sql.Date to have its own equals() method, comparing only the Date portion ?
Just a thought.
Any ideas will be appreciated.
Regards.
[ November 06, 2002: Message edited by: Paulo Salgado ]
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
Both java.sql.Date and java.util.Date represent the time portion of a date, to the resolution of 1 millisecond. A java.sql.Date will be used for SQL DATE, TIME, and DATETIME columns.
When you select the system date from the database, you should be getting a DATETIME, so you have to deal with the millisecnonds. Same thing happens when you create a java.util.Date with the no-argument constructor.
Paulo Salgado
Ranch Hand

Joined: Jan 18, 2002
Posts: 98
Michael,
I'm afraid I don't understand correctly what you say.
java.util.Date and java.sql.Date store both the date and time portion. That I understand and implied in my first posting.
Based on the JDBC specs (3.0 at least) the java.sql.Date class should be used to handle DATE columns, java.sql.Time class to handle TIME columns and java.sql.Timestamp class to handle TIMESTAMP columns. If previous specs state it differently please let me know.
I didn't see mention to a DATETIME type and, actually, I don't have that type available in the RDBMS I'm using. I'm using those in the specs.
My point is, if java.sql.Date is specified to handle a DATE SQL type, which doens't have a time portion in the database, why it's equals method compares the time portion ?
In other words, if I create a java.sql.Date with the current time, it will have the milliseconds there. But if I retrieve a DATE column from the DB the seconds and milliseconds portion will be zero. When I compare both objects, unless I'm extremely lucky, they will be different.
So, an object to handle the SQL DATE type is not performing the comparison as the Database does. It just seemed inconsistent to me, and I'm looking if there is a consistent answer somewhere, that I could not find.
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
Both Date and Timestamp types can represent fractions of a day. Timestamp represents the time part to the nanosecond, so it has a higher resolution than the Date type. And java.sql.Date is the appropriate Java type for a column of SQL type DATETIME. BTW, when you specify DATE in many relational databases, you actually get the SQL DATETIME type. Some databases like DB2 have seperate column types for DATE, TIME and DATETIME.
There is no standard Java type that represents a date without hours/minutes/seconds (AFAIK).
Paulo Salgado
Ranch Hand

Joined: Jan 18, 2002
Posts: 98
Michael,
thanks for your reply.
Regards.
[ November 12, 2002: Message edited by: Paulo Salgado ]
 
 
subject: java.sql.Date comparison
 
Similar Threads
Problem Reading/Writing Date
basic calendar questions
Equals and Hash Code
Losing time when manipulating a date time and sending it to database (java.sql.Date)
Updating Date