Adding hours, minutes and seconds to java SQL Date

Question

I'm having some trouble counting values from my data base with Java.

I'm trying to count some values by date from my DB, but they have hours, minutes and seconds. If I search with java.sql.Date's it just allows me to enter year, month and day, so the values with a date: year"-"month"-31 (hour:minutes:seconds != 00:00:00)" won't be counted.

Is there a way to add hours, minutes, and seconds to a java.sql.Date? If not, what would it be a better way to do it?

My code:

public static int getAmountPos(Connection con, Integer month, Integer year) {

        java.sql.Date date1 = java.sql.Date.valueOf(year+"-"+month+"-01");
        java.sql.Date date2 = java.sql.Date.valueOf(year+"-"+month+"-31");

        Integer amount = null;
        String sql = "SELECT COUNT(*) AS 'cantidad' "
                + " FROM table.point_of_service "
                + " WHERE registration_date between ? AND ?";

        PreparedStatement ps = con.getPreparedStatement(sql);
        ps.setDate(1, date1);
        ps.setDate(2, date2);

        ResultSet rs = ps.executeQuery();

        if(rs.next()) {
            amount = rs.getInt("cantidad");
        }

        return amount;
    }

Answer 1

The java.sql.Date class is not intended to hold information about time. All it holds is date (year, month, day). You should use java.sql.Timestamp for your needs.

Answer 2

tl;dr

Change the code to use (a) java.time types and (b) the Half-Open approach to span-of-time.

String sql = "SELECT COUNT(*) AS 'count_' "
           + "FROM table_.point_of_service_ "
           + "WHERE registration_date_ >= ? "  // Greater-than-or-equal.
           + "AND WHERE registration_date_ < ? ; ";  // Less-than (not 'or-equal') is Half-Open approach.

PreparedStatement ps = con.getPreparedStatement(sql);
ps.setObject( 1, YearMonth.of( 2016 , Month.FEBRUARY ).atDay( 1 ) );  // Calling `setObject` to handle `LocalDate` in JDBC 4.2 and later.
ps.setObject( 2, YearMonth.of( 2016 , Month.FEBRUARY ).plusMonths( 1 ).atDay( 1 ) );

java.sql

The java.sql types are intended only for moving data in and out of the database. Generally should not be used for business logic or manipulating date-time values.

• The java.sql.Date class represents date-only values without time-of-day and without time zone.
• The converse for java.sql.Time, a time-of-day with no date nor time zone.
• For a date-time, use java.sql.Timestamp. This value is always in UTC.

For the date-time, call getTimestamp on your ResultSet.

java.sql.Timestamp tsWhenRegistered = myResultSet.getTimestamp( "when_registered_" );

Of course, you must be calling upon a column with a matching data type in your database.

java.time

In Java 8 and later, use the java.time framework for your business logic.

Avoid the old java.util.Date/.Calendar classes as they have proven to be troublesome, confusing, and flawed. They have been supplanted by the java.time classes.

Convert from java.sql types to java.time types as soon as you have grabbed them from the database. Hopefully JDBC drivers will be updated to deal with java.time types directly, but until then do the conversion yourself.

Instant

An Instant is a moment on the timeline in UTC.

Instant instant = tsWhenRegistered.toInstant(); // From java.sql to java.time.

Time Zone

Apply a time zone (ZoneId) as makes sense in your context. Let's say your business is in Québec.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = instant.atZone( zoneId );

YearMonth

Now, the other way from java.time to java.sql as seen in your Question. Rather than pass Integer objects for year and month, use the YearMonth class. Remember these java.time classes are built into Java now. So you can make use of them throughout your code. This gives you type-safety and compiler checks, and a guarantee of valid values.

YearMonth yearMonth = YearMonth.of( 2016 , Month.FEBRUARY ); // Pass Month enum for type-safety and range-checking.
YearMonth yearMonth = YearMonth.of( 2016 , 2 );  // Or, pass month number 1-12.

Re-do the method shown in Question assuming "when_registered_" is date-time value rather than date-only as you seem to be suggesting in your Question (not quite clear).

public static int getAmountPos( Connection con, YearMonth yearMonth ) {
…

By the way, a tip: Appending an underscore to your database names precludes collisions with keywords in SQL. The SQL standard explicitly promises to never use a trailing underscore.

Half-Open

Do not use the SQL BETWEEN. In date-time work the best practice is the “Half-Open” approach where the beginning is inclusive while the ending is exclusive. The search logic is greater-than-or-equal to the beginning and less-than the ending (not less-than-or-equal the ending). This avoids the problem of determining end-of-day to the split-second.

YearMonth → LocalDate

From YearMonth we can get the first of that month and the first of the next month as date-only (LocalDate) values.

LocalDate localDateStart = yearMonthArg.atDay( 1 ); // Get first-of-month.
LocalDate localDateStop = yearMonthArg.plusMonths( 1 ).atDay( 1 ); // Get first of *next* month.

If your driver complies with JDBC 4.2 or later you should be able to pass these LocalDate objects to a PreparedStatement via the setObject method, and getObject for retrieving data.

Here is example code if your database column is defined as a standard SQL DATE type or a similar date-only type (no time-of-day, no time zone).

…
String sql = "SELECT COUNT(*) AS 'count_' "
           + "FROM table_.point_of_service_ "
           + "WHERE registration_date_ >= ? "  // Greater-than-or-equal.
           + "AND WHERE registration_date_ < ?";  // Less-than (not 'or-equal') is Half-Open approach.

PreparedStatement ps = con.getPreparedStatement(sql);
ps.setObject( 1, localDateStart );  // Calling `setObject` to handle `LocalDate` in JDBC 4.2 and later.
ps.setObject( 2, localDateStop );
…

If your driver cannot perform this way, substitute with java.sql.Date objects. The old date-time classes have new methods to facilitate such conversions. Here we need the java.sql.Date.valueOf method. Going the other direction when retrieving data from database, call toLocalDate.

…
ps.setDate( 1, java.sql.Date.valueOf( localDateStart ) );  // Converting `LocalDate` to `java.sql.Date`.
ps.setDate( 2, java.sql.Date.valueOf( localDateStop ) );
…

LocalDate + ZoneId → ZonedDateTime → java.sql.Timestamp

If your database column is not defined as a date-only type, but a date-time type, then we need to query with date-time objects rather than the LocalDate objects seen above. That means OffsetDateTime objects if your date is meant for UTC, or ZonedDateTime if your date has meaning in a certain other time zone.

Keep in mind that LocalDate objects have no real meaning as the date varies around the world at any given moment. We must apply a time zone to get the first moment of each day as ZonedDateTime objects. Be aware that the first moment of the day is not always 00:00:00.0 because of Daylight Saving Time and perhaps other anomalies. To convert from java.time to java.sql, we extract Instant objects from the ZonedDateTime objects, and finally convert to java.sql.Timestamp.

public Integer countForYearMonth ( Connection connArg , YearMonth yearMonthArg ) {
    // CAUTION: Pseudo-code. Ignoring real-world issues such as closing resources and handling exceptions. Never run, so never tested.

    LocalDate localDateStart = yearMonthArg.atDay( 1 ); // Get first-of-month.
    LocalDate localDateStop = yearMonthArg.plusMonths( 1 ).atDay( 1 ); // Get first of *next* month.

    // Give those LocalDate objects real meaning by applying a time zone.
    ZoneId zoneId = ZoneId.of( "America/Montreal" );  // Perhaps pass as argument rather than hard-code a particular time zone.
    ZonedDateTime zdtStart = localDateStart.atStartOfDay( zoneId );  // Inclusive of first moment of February… 2016-02-01T00:00:00.0-05:00[America/Montreal]
    ZonedDateTime zdtStop = localDateStop.atStartOfDay( zoneId ); // Inclusive of first moment of March… 2016-03-01T00:00:00.0-05:00[America/Montreal]

    // Business logic is complete. So convert to java.sql for database access.
    java.sql.Timestamp tsStart = java.sql.Timestamp.from ( zdtStart.toInstant() );  // February 1st 2016 at 5 AM UTC.
    java.sql.Timestamp tsStop = java.sql.Timestamp.from ( zdtStop.toInstant() );  // March 1st 2016 at 5 AM UTC.

    Integer amount = null;
    String sql = "SELECT COUNT(*) AS 'count_' "
               + "FROM some_table_ "
               + "WHERE when_registered_ >= ? "  // Greater-than-or-equal.
               + "AND when_registered_ < ? "  // Less-than (not 'or-equal') is Half-Open approach.
               + "; ";

    PreparedStatement ps = connArg.getPreparedStatement(sql);
    ps.setDate( 1, tsStart );
    ps.setDate( 2, tsStop );

    ResultSet rs = ps.executeQuery();

    if( rs.next() ) {
        amount = rs.getInt( "count_" );
    }
    return amount;
}