ResultSet getDate() is returning incorrect and correct dates

Question

I am creating a list of LocalDate's from a table of holidays. The dates are all correct in the database table, but when I use the ResultSet getDate() method and populate a list with all the dates 4 of the dates are wrong and 6 are correct. I am looking for any help figuring out why this is the case.

final LocalDate currentDay = LocalDate.now();
final List<LocalDate> holidays = getHolidays(currentDay);

//This method takes in a date and calculates the holidays that occur that year
public static List<LocalDate> getHolidays(final LocalDate date) {
 final List<LocalDate> holidayList = new ArrayList<LocalDate>();

 try {
   final PreparedStatement holidaysByYearQuery = conn.prepareStatement(
      "SELECT fulldate FROM holidays WHERE YEAR(fulldate) = ?");

    holidaysByYearQuery.setObject(1, date.getYear());

    final ResultSet holidaysByYearResult = holidaysByYearQuery.executeQuery();

    while (holidaysByYearResult.next()) {
      final Date holidate = (Date) holidaysByYearResult.getObject(1);
      final LocalDate holiday = holidate.toLocalDate();
      holidayList.add(holiday);
    }
  } catch (final SQLException e) {
    LOGGER.log(Level.WARNING, "Could not query holiday table: " + e.getMessage());
  }
  return holidayList;
 }

// This is the query used in Java:
com.mysql.cj.jdbc.PreparedStatement@1c27c773: SELECT fulldate FROM holidays WHERE YEAR(fulldate) = 2019

// Here is the list that is returned from the method
[2018-12-31, 2019-02-17, 2019-04-19, 2019-05-20, 2019-07-01, 2019-08-05, 2019-09-02, 2019-10-14, 2019-12-24, 2019-12-25]

Here is the database table information. It is very basic.

CREATE TABLE holidays( 
province VARCHAR(255), 
holiday VARCHAR(255), 
fulldate DATETIME);

INSERT INTO holidays(province, holiday, fulldate) VALUES
('Ontario', 'New Years', '2019-01-01'), 
('Ontario', 'Family Day', '2019-02-18'), 
('Ontario', 'Good Friday', '2019-04-19'), 
('Ontario', 'Victoria Day', '2019-05-20'), 
('Ontario', 'Canada Day', '2019-07-01'), 
('Ontario', 'Civic Holiday', '2019-08-05'), 
('Ontario', 'Labour Day', '2019-09-02'), 
('Ontario', 'Thanksgiving', '2019-10-14'), 
('Ontario', 'Christmas Day', '2019-12-25'), 
('Ontario', 'Boxing Day', '2019-12-26');

// This is the select result from the query above

+---------------------+
|       fulldate      |
+---------------------+
| 2019-01-01 00:00:00 |
+---------------------+
| 2019-02-18 00:00:00 |
+---------------------+
| 2019-04-19 00:00:00 |
+---------------------+
| 2019-05-20 00:00:00 |
+---------------------+
| 2019-07-01 00:00:00 |
+---------------------+
| 2019-08-05 00:00:00 |
+---------------------+
| 2019-09-02 00:00:00 |
+---------------------+
| 2019-10-14 00:00:00 |
+---------------------+
| 2019-12-25 00:00:00 |
+---------------------+
| 2019-12-26 00:00:00 |
+---------------------+

As you can see the 2 dates on each end of the list are incorrect. I assume something is happening with the getDate() method. Can anyone shed some light on this? Cheers.

Answer 1

Why are you mixing the terrible legacy class Date with the modern LocalDate? And why are you casting (Date)?

SQL-standard DATE

If your column is of a type akin to the SQL-standard DATE, then use LocalDate with a JDBC driver compliant with JDBC 4.2 or later.

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

SQL-standard TIMESTAMP WITH TIME ZONE

If your column is of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE, then you must account for time zone to determine a date.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZoneId z = ZoneId.of( "Australia/Sydney" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;
LocalDate ld = zdt.toLocalDate() ;

SQL-standard TIMESTAMP WITHOUT TIME ZONE

If your column is of a data type akin to the SQL-standard TIMESTAMP WITHOUT TIME ZONE such as the MySQL type DATETIME, use the Java type LocalDateTime. This type has only a date and time-of-day without the context of a time zone or offset-from-UTC. So this type does not represent a moment.

LocalDateTime ldt = myResultSet.getObject( … , OffsetDateTime.class ) ;
LocalDate ld = ldt.toLocalDate() ;