Search code examples
javavalidationdatecompareto

Java compareTo Dates cannot be equal

I am trying to compare 2 dates, the first date is from the MySQL database, and the second is from the current date.

as you can see below, there are different dates in the database

Dates records in database But the problem is that I got 3 if statements, which should tell my program if the Database date is before, after or equal the current date. The Before and After statements should work, but it can see the date 2018-06-12 should be equal to the current date so it ends in the "before statement".

Hope you can see what I have done wrong.

private static void Resetter() throws ParseException, SQLException {
    String host = "****";
    String username = "root";
    String mysqlpassword = "";

    //Querys
    String query = "select * from accounts";
    String queryy = "update accounts set daily_search_count = 0 where id = ?";
    Connection con = DriverManager.getConnection(host, username, mysqlpassword);
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery(query);

    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    dateFormat.setTimeZone( TimeZone.getTimeZone( "UTC" ));
    Date currentDate = new Date();

    while(rs.next()){
        System.out.println(dateFormat.format(currentDate));
        if (rs.getDate(5).compareTo(currentDate) > 0) {
           // System.out.println("Database-date is after currentDate");
        } else if (rs.getDate(5).compareTo(currentDate) < 0) {
           // System.out.println("Database-date is before currentDate");
            PreparedStatement updatexdd = con.prepareStatement(queryy);
            updatexdd.setInt(1, rs.getInt(1));
            int updatexdd_done = updatexdd.executeUpdate();
        } else if (rs.getDate(5).compareTo(currentDate) == 0) {
           // System.out.println("Database-date is equal to currentDate");
        } else {
            System.out.println("Any other");
        }
    }
}
Solution

  • tl;dr

    LocalDate today = LocalDate.now( ZoneOffset.UTC ) ;
Instant instant = myResultSet.getObject( … , Instant.class ) ;  // Retrieve a `TIMESTAMP WITH TIME ZONE` value in database as an `Instant` for a date with time-of-day in UTC with a resolution as fine as nanoseconds.
LocalDate ld = instant.atOffset( ZoneOffset.UTC ).toLocalDate() ;  // Extract a date-only value without time-of-day and without time zone.

if ( ld.isBefore( today ) ) { … }       // Compare `LocalDate` objects.
else if ( ld.isEqual( today ) ) { … }
else if ( ld.isAfter( today ) ) { … }
else { … handle error }

    java.time

    You are using, and misusing, troublesome old date-time classes.

    As others pointed out:

    • A SQL-standard DATE type holds only a date without a time-of-day and without a timezone
    • The legacy java.util.Date class is misnamed, holding both a date and a time-of-day in UTC.
    • The legacy java.sql.Date class pretends to hold only a date but actually has a time-of-day because this class inherits from the one above, while the documentation tells us to ignore that fact in our usage. (Yes, this is confusing, and is a bad design, a clumsy hack.)

    Never use the java.util.Date, java.util.Calendar, java.sql.Timestamp, java.sql.Date, and related classes. Instead, use only the sane java.time classes. They lead the industry in clean-design and depth of understanding of date-time handling gleaned from the experience of their predecessor, the Joda-Time project.

    For date-only value, stored in SQL-standard database type of DATE, use java.time.LocalDate.

    LocalDate ld = myResultSet.get( … , LocalDate.class ) ;  // Retrieving from database.
myPreparedStatement.setObject( … , ld ) ;  // Storing in database.

    For a date with time-of-day in UTC value, stored in a SQL-standard database type of TIMESTAMP WITH TIME ZONE, use java.time.Instant. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). 

    Instant instant = myResultSet.get( … , Instant.class ) ;  // Retrieving from database.
myPreparedStatement.setObject( … , instant ) ;  // Storing in database.

    For comparing in Java, use the isEqual, isBefore, isAfter, equals, or compare methods.

    Boolean overdue = someLocalDate.isAfter( otherLocalDate ) ;

    Time zone

    Time zone is crucial in determining a date and a time-of-day from a moment (Instant/TIMESTAMP WITH TIME ZONE).

    After retrieving your TIMESTAMP WITH TIME ZONE value from the database as an Instant, adjust into the time zone or offset-from-UTC whose wall-clock time you want to use in perceiving a date & time-of-day. For a time zone, apply a ZoneId to get a ZonedDateTime object. For an offset-from-UTC, apply a ZoneOffset to get a OffsetDateTime object. In either case, extract a date-only value by calling toLocalDate to get a LocalDate object.

    In your case, you apparently want to perceive the date as UTC. So apply the constant, ZoneOffset.UTC to get an OffsetDateTime

    OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;  
LocalDate ld = odt.toLocalDate() ;   // Extract a date-only value without time-of-day and without time zone.

    We want to compare with current date in UTC.

    LocalDate today = LocalDate.now( ZoneOffset.UTC ) ;  // Specify the offset/zone by which you want to perceive the current date.

    Compare.

    if ( ld.isBefore( today ) ) { … }
else if ( ld.isEqual( today ) ) { … }
else if ( ld.isAfter( today ) ) { … }
else { … handle error }

    ISO 8601

    Avoid unnecessarily using custom formats such as "yyyy/MM/dd". Use standard ISO 8601 formats whenever possible.

    For a date-only value, that would be YYYY-MM-DD. 

    String output = LocalDate.now().toString() ;  // Ex: 2018-01-23

    Example with H2

    Here is a full example of writing, querying, and reading LocalDate objects from a database column of SQL-standard DATE type.

    Using the H2 Database Engine, as I am not a MySQL user. Creating an in-memory database rather than writing to storage. I assume the code would be nearly the same for MySQL. 

    try (
    Connection conn = DriverManager.getConnection( "jdbc:h2:mem:trashme" )
) {
    String sql = "CREATE TABLE " + "tbl_" + " (\n" +
                     "  uuid_ UUID DEFAULT random_uuid() , \n" +  // Every table should have a primary key.
                     "  when_ DATE \n" +                          // Three columns per the Question.
                     " );";
    try (
        Statement stmt = conn.createStatement() ;
    ) {
        stmt.execute( sql );
    }

    sql = "INSERT INTO tbl_ ( when_ ) VALUES ( ? ) ;";
    LocalDate start = LocalDate.of( 2018 , Month.JANUARY , 23 );
    LocalDate ld = start;  // Keep the `start` object for use later.
    try (
        PreparedStatement ps = conn.prepareStatement( sql )
    ) {
        for ( int i = 1 ; i <= 10 ; i++ ) {
            ps.setObject( 1 , ld );
            ps.executeUpdate();
            // Prepare for next loop.
            int randomNumber = ThreadLocalRandom.current().nextInt( 1 , 5 + 1 ); // Pass minimum & ( maximum + 1 ).
            ld = ld.plusDays( randomNumber ); // Add a few days, an arbitrary number.
        }
    }

    // Dump all rows, to verify our populating of table.
    System.out.println( "Dumping all rows: uuid_ & when_ columns." );
    sql = "SELECT uuid_ , when_ FROM tbl_ ; ";
    int rowCount = 0;
    try (
        Statement stmt = conn.createStatement() ;
        ResultSet rs = stmt.executeQuery( sql ) ;
    ) {
        while ( rs.next() ) {
            rowCount++;
            UUID uuid = rs.getObject( 1 , UUID.class );
            LocalDate localDate = rs.getObject( 2 , LocalDate.class );
            System.out.println( uuid + " " + localDate );
        }
    }
    System.out.println( "Done dumping " + rowCount + " rows." );


    // Dump all rows, to verify our populating of table.
    System.out.println( "Dumping rows where `when_` is after " + start + ": uuid_ & when_ columns." );
    sql = "SELECT uuid_ , when_ FROM tbl_ WHERE when_ > ? ; ";
    rowCount = 0; // Reset count.
    final PreparedStatement ps = conn.prepareStatement( sql );
    ps.setObject( 1 , start );
    try (
        ps ;
        ResultSet rs = ps.executeQuery() ;
    ) {
        while ( rs.next() ) {
            rowCount++;
            UUID uuid = rs.getObject( 1 , UUID.class );
            LocalDate localDate = rs.getObject( 2 , LocalDate.class );
            System.out.println( uuid + " " + localDate );
        }
    }
    System.out.println( "Done dumping " + rowCount + " rows." );

} catch ( SQLException eArg ) {
    eArg.printStackTrace();
}

    When run.

    Dumping all rows: uuid_ & when_ columns.
e9c75998-cd67-4ef9-9dce-6c1eed170387 2018-01-23
741c1452-e224-4e5e-95bc-904d8db58b39 2018-01-27
413de43c-a1be-40b6-9ccf-a9b7d9ba873c 2018-01-31
e2aa148f-48b6-4be6-a0fe-f2881b6b5a63 2018-02-03
f498003c-2d8b-446e-ac55-6d7568ce61c3 2018-02-06
c41606d7-8c05-4bba-9f8e-2a0d1f1bb31a 2018-02-09
3df3abe3-1865-4632-99c4-6cd74883c1ee 2018-02-10
914153fe-34f2-4e4f-a91b-944314994839 2018-02-13
96436bdf-80ee-4afe-b55d-f240140ace6a 2018-02-16
82b43f7b-077d-45c1-8c4f-f5b30dfdd44a 2018-02-19
Done dumping 10 rows.
Dumping rows where `when_` is after 2018-01-23: uuid_ & when_ columns.
741c1452-e224-4e5e-95bc-904d8db58b39 2018-01-27
413de43c-a1be-40b6-9ccf-a9b7d9ba873c 2018-01-31
e2aa148f-48b6-4be6-a0fe-f2881b6b5a63 2018-02-03
f498003c-2d8b-446e-ac55-6d7568ce61c3 2018-02-06
c41606d7-8c05-4bba-9f8e-2a0d1f1bb31a 2018-02-09
3df3abe3-1865-4632-99c4-6cd74883c1ee 2018-02-10
914153fe-34f2-4e4f-a91b-944314994839 2018-02-13
96436bdf-80ee-4afe-b55d-f240140ace6a 2018-02-16
82b43f7b-077d-45c1-8c4f-f5b30dfdd44a 2018-02-19
Done dumping 9 rows.

    About java.time

    The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

    The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

    To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

    You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

    Where to obtain the java.time classes?

    The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.