JDBC java.sql.Date() comparison is giving false

Question

i am curious why the if comparison is giving false:

I am inserting a date into a date field using this code:

preparedStatement.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
System.out.println("sql date" + new java.sql.Date(new java.util.Date().getTime()));
output is: 2014-07-16

After the insertion i query the database to find out if records has been inserted today:

    String sql = select MAX (last_modified) as last_modified from mydb.mytable
    ResultSet rs = stmt.executeQuery(sql);

    while (rs.next())
    {

        if (rs.getDate(1).equals(new java.sql.Date(new java.util.Date().getTime())))
        {
            System.out.println("Same Date in here not need to update");
         }
        else
        {
            System.out.println("Dates are different");
        }

        System.out.println("date from db: " + rs.getDate(1));  
        System.out.println("new sql date: " + new java.sql.Date(new java.util.Date().getTime()));: 
    }

The output is:

Dates are different
date from db: 2014-07-16
new sql date: 2014-07-16

I think both dates are similar and both are casting to match java.sql.Date, maybe the condition is not correct.

I Appreciate any help to understand this behavior.

Solution 1

You are comparing Date and long

if (rs.getDate(1).equals(new java.sql.Date(new java.util.Date().getTime())))

change it to

if (rs.getDate(1).getTime() == System.currentTimeInMillis())

from your commment you want day level precision so

    Calendar startOfToday = Calendar.getInstance();
    Calendar endOfToday = Calendar.getInstance();
    endOfToday.setTime(startOfToday.getTime());

    startOfToday.set(Calendar.HOUR_OF_DAY, 0);
    startOfToday.set(Calendar.MINUTE, 0);
    startOfToday.set(Calendar.SECOND, 0);
    startOfToday.set(Calendar.MILLISECOND, 0);

    endOfToday.set(Calendar.HOUR_OF_DAY, 23);
    endOfToday.set(Calendar.MINUTE, 59);
    endOfToday.set(Calendar.SECOND, 59);
    endOfToday.set(Calendar.MILLISECOND, 999);

    long transactionDate = rc.getDate(1).getTime();
    if(transactionDate >= startOfToday.getTimeInMillis() && transactionDate <= endOfToday.getTimeInMillis()){

    }

Solution 2

You are working too hard. There are easier and clearer ways to do such comparisons in modern Java.

The old java.util.Date, java.sql.Date, and .Calendar classes are notoriously troublesome and should be avoided.

java.sql.Date is a Hack

A java.sql.Date is merely a java.util.Date with its time-of-day set to 00:00:00 (UTC). This is an old lame hack to get around the fact that early versions of Java lacked a class to represent a date-only without any time-of-day or time zone.

java.time

Now Java 8 does have a date-only class, LocalDate, found in the new java.time package.

Methods have been added to the java.sql.Date class to convert to/from LocalDate.

LocalDate localDate = someSqlDate.toLocalDate();
LocalDate today = LocalDate.now( ZoneOffset.UTC ); // Pass a time zone to get current date in UTC for fair comparison.
boolean localDateIsToday = localDate.isEqual( today );

Going the other way…

java.sql.Date sqlDate = java.sql.Date.valueOf( someLocalDate );

JDBC 4.2 Driver

If your JDBC driver has been updated to support JDBC 4.2, then according to the JDBC 4.2 Spec Update (item # 21) you can call the getObject method on a ResultSet to directly obtain a LocalDate object.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

Likewise, call PreparedStatement::setObject to pass a LocalDate object to a SQL DATE type column in your database.

myPreparedStatement.setObject( … , localDate ) ;

Avoid java.sql.Date altogether.

Joda-Time

The java.time package was inspired by the Joda-Time library. You can use it in earlier versions of Java where java.time is not available. Joda-Time too offers a LocalDate class.

Solution 3

Change your test to:

if (rs.getDate(1).toString().equals(new java.sql.Date(System.currentMillis()).toString())

Comments

[Israelm]

If i do that i get: Cannot invoke equals(Date) on the primitive type long

[jmj]

fixed, this comparission however compare date to the millisecond precision from your code it looks like you need that

[Israelm]

this is giving me this output: rs gettime: 1405486800000 rs system millisec: 1405544365833 and the result is still "dates are different" i would like to know if the transactions happened on the same date 2014-07-16

[Israelm]

same case in here, since some time occurred since the inesrtion to the comparison i am giving this output: rs gettime: 1405486800000 rs system milisecs: 1405544807311 Dates are different date from db: 2014-07-16 new sql date2014-07-16

[Bohemian]

Try that change (I don't have an IDE handy to test this, but it could work because SQL dates ignore the time part)

[Israelm]

Wow! outstanding answer! totally worked. Thanks a lot!

[Mark Rotteveel]

You can only get a LocalDate in JDBC 4.2 if the driver has this implemented.

[Basil Bourque]

@MarkRotteveel Your comment is incorrect. Re-read my answer. You can obtain a LocalDate object either by a factory method on the LocalDate class or by calling toLocalDate on a java.sql.Date object. Both of those routes have nothing to do with your JDBC driver. My answer explains the only part relating to your JDBC driver, calling the getObject on a ResultSet. Notice the large heading saying "JDBC 4.2 Driver".

[Mark Rotteveel]

My comment was referring exactly to that part of your post, that is why I included "in JDBC 4.2": using getObject to obtain a LocalDate (or setObject to set one) only works if the driver implementation already supports Java 8 and has followed the requirements in JDBC 4.2. I could have been more specific, but I thought that as that was the only part of your post that was referring to JDBC 4.2 mentioning JDBC 4.2 would be sufficient.

[Israelm]

it actually works! statement: System.out.println(new java.sql.Date(System.currentTimeMillis()).toString()); output: 2014-07-22

[Bohemian]

@Israelm exactly: A java.sql.Date is a normal except it ignores the hours, minutes and seconds in its toString() impl.

[Basil Bourque]

@MarkRotteveel It seems I misunderstood the point of your comments here, way back when. But I still do not get it. Are you saying support for LocalDate in JDBC 4.2 is optional? In my reading of the spec, support for LocalDate seems to be required. Please correct me if I am wrong there; and if possible, cite the spec. And please correct me if I have still not understood the point of your comments here.