Jdbc ResultSet: I need a getDateTime, but there is only getDate and getTimeStamp

Going the full nine yards with getTimestamp()

When using getTimestamp() to fetch date and time, you get invaluable nanosecond precision. Always remember to check for null values before working with the fetched data:

// No Nulls Allowed 🚧 : Check before you wreck... your code
if (resultSet.getTimestamp("column_name") != null) {
    LocalDateTime localDateTime = resultSet.getTimestamp("column_name").toLocalDateTime();
    // localDateTime ready for rock-n-roll 🎸
} else {
    // Handle missing value scenario
}


Once the value has been retrieved, you may need to format it for display. Java 8's DateTimeFormatter is your ally here:

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
String formattedDateTime = localDateTime.format(formatter); // Formatted and ready to go! 🚀


A compressed guide to JDBC and date-time types

When interfacing JDBC and date-time types, there are some golden rules to keep in mind.

Direct exchange with JDBC 4.2 and java.time

JDBC 4.2 and above lets you directly exchange java.time objects with the database using the handy setObject and getObject methods:

// Inserting a LocalDateTime, as easy as pie 🥧
preparedStatement.setObject(1, localDateTime);

// Reclaiming a LocalDateTime, like finding a lost puppy 🐶
LocalDateTime localDateTime = resultSet.getObject("column_name", LocalDateTime.class);


Sidestepping common traps

Avoid the potential inconsistencies of resultSet.getString() for date-time values. It could append undesired fractions like ".0" to your time data. It's safer to stick with getTimestamp().

Pre-Java 8, consider ThreeTen-Backport or Joda-Time. These modern date-time classes offer familiar usability:

DateTime dateTime = new DateTime(resultSet.getTimestamp("column_name")); // A step back into the past ⌛


Call in SQL-side formatting when necessary

While not ideal, SQL's DATE_FORMAT function can be a lifesaver, especially when you need the database to return neatly formatted date-time values:

SELECT DATE_FORMAT(your_datetime_column, '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM your_table;


Retrieve in Java as follows:

String formattedDateTime = resultSet.getString("formatted_datetime"); // SQL does the dirty work 💪


When in doubt, rigorously test

To round it off, thorough testing is essential. Date-time handling can vary significantly among databases and JDBC drivers. So, buckle up and conduct comprehensive testing. You wouldn't want any surprises when working with multiple databases or planning a migration.