Query filter comparing Dates

Hi,

I am using an indexed java.util.Date field in my Entity and I came across some weird behaviour in query filters comparing this Date field. Could You please clarify why are there zero-size result collections in the first pack of queries and non-zero size results in the second pack ? I am comparing 2 dates which should be equal because they are showing exactly same inner millisecond count. I attached the whole test case.

public static void main(String[] args) {
   setUp();
   Date date = ClassWithDate.getCorrectDate(new Date());
   PersistenceManager pm = getPMF().getPersistenceManager();

   // these queries should find 1 item

   assertQueryResultSize(0, pm.newQuery(ClassWithDate.class, "this.date == datum"), date);
   assertQueryResultSize(0, pm.newQuery("SELECT FROM ClassWithDate WHERE date == datum PARAMETERS java.util.Date datum" ), date);
   assertQueryResultSize(1, pm.newQuery(ClassWithDate.class, "this.date.equals(datum)"), date);
   assertQueryResultSize(1, pm.newQuery(ClassWithDate.class, "this.type.equals(\""+ TYPE +"\") && this.date.equals(datum)"), date);

   // these queries should find 0 item
   assertQueryResultSize(0, pm.newQuery(ClassWithDate.class, "this.date < datum"), date);
   assertQueryResultSize(0, pm.newQuery(ClassWithDate.class, "this.date.before(datum)"), date);
   assertQueryResultSize(1, pm.newQuery(ClassWithDate.class, "this.date > datum"), date);
   assertQueryResultSize(1, pm.newQuery(ClassWithDate.class, "this.type.equals(\""+ TYPE + "\") && this.date > datum"), date);
   assertQueryResultSize(0, pm.newQuery(ClassWithDate.class, "this.date.after(datum)"), date);

   pm.close();
}

Thank you for the report and the test case. Working with dates could be very tricky due to time zones.

In this specific test case, you will get the correct results (at least using standard =, <, > query operators, Java functions are non-standard and not recommended in queries if there is a choice), if you use a different calendar:

    Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
    // GregorianCalendar calendar = new GregorianCalendar();

All date and time values are stored in the database as time (e.g. in milliseconds) since 1.1.1970 UTC. For TemporalType.DATE the hours, minutes, seconds, and milliseconds are 0, but relative to UTC. Therefore, the parameter to the query should also be set as a UTC pure date value.

I modified the test case, so it works now with UTC timezone in Calendar and I get for the following tests :

// these queries should find 1 item
assertQueryResultSize(1, pm, "this.date == datum", date);
assertQueryResultSize(1, pm, "this.type.equals(\""+ TYPE +"\") && this.date == datum", date);
assertQueryResultSize(0, pm, "this.date.equals(datum)", date);

// these queries should find 0 item
assertQueryResultSize(0, pm, "this.date < datum", date);
assertQueryResultSize(1, pm, "this.date.before(datum)", date);
assertQueryResultSize(0, pm, "this.date > datum", date);
assertQueryResultSize(0, pm, "this.type.equals(\""+ TYPE + "\") && this.date > datum", date);
assertQueryResultSize(0, pm, "this.date.after(datum)", date);

These results:

ClassWithDate.date before saving: Tue Feb 02 01:00:00 CET 2021
Match found for filter    : this.date == datum
Date from DB instance    : Tue Feb 02 00:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

Match found for filter    : this.type.equals("type1") && this.date == datum
Date from DB instance    : Tue Feb 02 00:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

Match found for filter    : this.date.before(datum)
Date from DB instance    : Tue Feb 02 00:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

So, the equals() and before() filters does not return correct result imho.

Also when there is a match (for ==), you can see in the results, that the 2 dates have different time in same timezone. How can they be equal than ?

I launched the test also after I removed the         
@Temporal(TemporalType.DATE) 
annotation from ClassWithDate.date and I got quite different results.

// these queries should find 1 item
assertQueryResultSize(1, pm, "this.date == datum", date);
assertQueryResultSize(1, pm, "this.type.equals(\""+ TYPE +"\") && this.date == datum", date);
assertQueryResultSize(1, pm, "this.date.equals(datum)", date);

// these queries should find 0 item
assertQueryResultSize(0, pm, "this.date < datum", date);
assertQueryResultSize(0, pm, "this.date.before(datum)", date);
assertQueryResultSize(0, pm, "this.date > datum", date);
assertQueryResultSize(0, pm, "this.type.equals(\""+ TYPE + "\") && this.date > datum", date);
assertQueryResultSize(0, pm, "this.date.after(datum)", date);

These results:

ClassWithDate.date before saving: Tue Feb 02 01:00:00 CET 2021
Match found for filter    : this.date == datum
Date from DB instance    : Tue Feb 02 01:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

Match found for filter    : this.type.equals("type1") && this.date == datum
Date from DB instance    : Tue Feb 02 01:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

Match found for filter    : this.date.equals(datum)
Date from DB instance    : Tue Feb 02 01:00:00 CET 2021
Date from filter param    : Tue Feb 02 01:00:00 CET 2021

All the filters work as expected and the result shows same dates for DB object and filter argument. So it seems to me that the         @Temporal(TemporalType.DATE) annotation changes the behaviour of the stored date to an incorrect way.

Using date and time values is very confusing, partly because of the way that java.util.Date and its subclasses are implemented. The way that ObjectDB functions in this context may require improvements. However, since such improvement may affect existing applications that use ObjectDB (or may require maintaining multiple modes), changes are considered very carefully. Hopefully the following information and the attached revised test case will help.

ObjectDB and JPA 2.0 support 3 basic date/time types:

1. java.sql.Date - represents date only (e.g. 2019-12-31).
2. java.sql.Time - represents time only (e.g. 23:59:59).
3. java.sql.Timestamp - represents date and time (e.g. 2019-12-31 23:59:59).

These types are subclasses of java.util.Date, and therefore hold internally elapsed time since January 1, 1970, 00:00:00 GMT. The class java.util.Date itself is ambiguous. If used, the @Temporal annotation must indicate which of the three specific types above to use.

When stored in the database, or sent as a parameter in a query, java.sql.Date and java.sql.Time are converted from local elapsed time to GMT elapsed time. An opposite conversion is done on retrieval. This way the values of Date and Time instances are represented in the database in a uniform way and clients in different time zones will see the same date and time (converted to local time) when retrieved from the database.

Timestamp is different, since it represents a point in time since January 1, 1970, 00:00:00 GMT (i.e. elapsed time), so no conversion is performed when it is stored or retrieved.

Apparently in your test case there is a mix between types. The entity includes java.sql.Date instances (although specified as TemporalType.DATE java.util.Date), but the parameter to the query is sent as java.util.Date, which by default is considered as a Timestamp. Therefore, the database contains converted elapsed times and the parameter is not converted.

Attached you can see a revised version of your test case that uses java.sql.Date all the way. Note that the answer in #2 above will not help in this case. You can use a local calendar, just make sure you use java.sql.Date all the way.

Thanks for explanation. Using everywhere java.sql.Date indeed solves the not working filters.