1

I have this piece of code

while(activecheck.next()){
    Date status;
    String vincheck;
    Date curr = new Date();
    int datecheck;
    status = activecheck.getDate(8);
    vincheck = activecheck.getString(2);
    String update = "UPDATE Auctions SET status = '"+inactive+"' WHERE vin = '"+vincheck+"'";
    datecheck = status.compareTo(curr);

    if(datecheck < 0){  
        stmt6.executeUpdate(update);
    }
}

Which iterates through a mysql table checking for inactive bids. I am trying to check whether the date and time listed in the sql row has been passed by the current time. However, whenever I do this, it seems to only be comparing the dates, and not the times. What could be the cause of this?

This is the format I am using : yyyy-MM-dd HH:mm:ss`

Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
cccc
  • 65
  • 1
  • 7
  • 5
    Your query is liable to sql injections. You should use prepared statements. – cdaiga Apr 13 '18 at 17:01
  • 1
    is there a reason we are updating RBAR (row by agonizing row)? and not just doing it in one fell swoop... `UPDATE Auctions SET status = 'inactive' WHERE col8 < NOW() AND ...` *whatever criteria is used in the* activecheck *cursor query* ... – spencer7593 Apr 13 '18 at 17:15
  • Thanks for the concern but its just a school project – cccc Apr 13 '18 at 17:15
  • @spencer7593 no there is no reason, im just a noob and didnt know I could do it like that – cccc Apr 13 '18 at 17:17
  • [What does your step debugger tell you?](http://stackoverflow.com/questions/25385173/what-is-a-debugger-and-how-can-it-help-me-diagnose-problems) –  Apr 13 '18 at 20:59

2 Answers2

1

You should use type which is called Timestamp instead of the date. This way you will cover the date and the current time 

  Timestamp timestamp = new Timestamp(System.currentTimeMillis());
Milos
  • 394
  • 2
  • 19
  • 1
    Still better, use `Instant` and `Instant.now()` and `activecheck.getObject(8, Instant.class)` and finally `if (status.isBefore(curr))`. Or possibly `LocalDateTime`, depending on the datatype of the database column. – Ole V.V. Apr 13 '18 at 17:19
  • Yes it certainly depends on what type is in the database column. @OleV.V. – Milos Apr 13 '18 at 17:24
  • FYI, the troublesome `java.sql.Timestamp` class was supplanted years ago by the modern *java.time* classes, specifically [`Instant`](https://docs.oracle.com/javase/10/docs/api/java/time/Instant.html). – Basil Bourque Apr 14 '18 at 19:54
1

tl;dr

if( 
    myResultSet.getObject( … , Instant.class )  // Retrieve `Instant` (a moment in UTC) from database using JDBC 4.2 or later.
               .isBefore( Instant.now() )       // Comparing to the current moment captured in UTC.
)
{ 
    … 
}

java.util.Date versus java.sql.Date

You may be confusing this pair of unfortunately mis-named classes. The first is a date-with-time type, in UTC. The second is a date-only type. Actually the second pretends to be a date-only type but actually has a time-of-day set to 00:00:00. Even worse, the second inherits from the first, but the documentation instructs us to ignore that fact.

Confusing? Yes. These awful classes are very poorly designed. Avoid them.

java.time

You are using terribly troublesome old date-time classes that were supplanted years ago by the java.time classes.

Instant

The java.util.Date class is replaced by 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).

LocalDate

The java.sql.Date class is replaced by java.time.LocalDate. The LocalDate class represents a date-only value without time-of-day and without time zone.

JDBC 4.2

As of JDBC 4.2 and later, you can directly exchange java.time classes with your database. No need to ever use java.sql or java.util date-time types again.

Tip: Make a habit of always using a PreparedStatement to avoid SQL Injection risk. Not really any more work once you get used to it.

Instant instant = Instant.now() ;  // Capture the current moment in UTC.
myPreparedStatement.setObject( … , instant ) ;

And retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Smart objects, not dumb strings

This is the format I am using : yyyy-MM-dd HH:mm:ss`

Date-time values stored in a database do not have a “format”. Those values are stored by some internally-defined mechanism that does not concern us. They are not strings (not in any serious database, that is).

Your database, and your Java date-time objects, can parse a string representing a date-time value to create that value. And they can generate a string to represent that value. But the string and the date-time value are distinct and separate, and should not be conflated.

Use java.time objects to exchange date-time values with your database, not mere strings, just as you would for numbers and other data types your database comprehends. Use strings only for communicating textual values.

Compare

To compare your retrieved values against the current moment, use the isBefore, isAfter, and equals methods of Instant class.

Instant now = Instant.now() ;
…
Instant instant = myResultSet.getObject( … , Instant.class ) ;
boolean isPast = instant.isBefore( now ) ;
if ( isPast ) { 
    … 
}

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154