Hi, Unfortunately, Joda time types are not supported by Ignite, thus as every unsupported type, it's treated inside as a BinaryObject. The comparison is performed on hash codes of BinaryObjects representing DateTime-s, hence unpredictable results.
I would recommend you use ScanQuery <https://apacheignite.readme.io/v2.1/docs/cache-queries#section-scan-queries> . Another option that could work, is to provide a custom set of custom SQL functions <https://apacheignite.readme.io/v2.1/docs/miscellaneous-features#section-custom-sql-functions> in a similar manner as below public static class JodaTimeSqlFunctions { @QuerySqlFunction public static int compareDateTime(BinaryObject dateTime1BinaryObject, BinaryObject dateTime2BinaryObject) { DateTime dateTime1 = dateTime1BinaryObject.deserialize(); DateTime dateTime2 = dateTime2BinaryObject.deserialize(); return dateTime1.compareTo(dateTime2); } ... } and then use them in your queries like this SqlFieldsQuery qry = new SqlFieldsQuery("select _val from Person where compareDateTime(birthDate, ?) >= 0"); qry.setArgs(startDate); But I suppose, ScanQuery approach is better and more vivid. Kind regards, Alex. On Wed, Aug 2, 2017 at 4:01 PM, cszczotka [via Apache Ignite Users] < [email protected]> wrote: > Hi, > I would like to run query where arguments are org.joda.time.DateTime on > apache ignite 2.1. Something like: > > > > > > > *DateTime startDate = new DateTime(2000, 1, 1, 10, 42, DateTimeZone.UTC); > DateTime endDate = new DateTime(2005, 12, 30, 10, 42, DateTimeZone.UTC); > SqlQuery<Long, Person> query = new SqlQuery<>(Person.class , "birthDate >= > ? and birthDate <= ?"); query.setArgs(startDate, endDate); List result = > personCache.query(query).getAll();* > > but this always return me 0 records. The personCache has set defined query > filed birthdate as: > *<entry key=" birthDate " value="org.joda.time.DateTime"/>* > > The interesting is if I do conversion DateTime to joda LocalDate: > *query.setArgs(startDate. > toLocalDate(), endDate. toLocalDate()); * > I get some part of records but not all which satisfy this condition. All > records I’m getting if I switch to ignite version 1.9. > > I see that this is similar topic: http://apache-ignite-users. > 70518.x6.nabble.com/How-to-configure-user-data-type-for- > sql-queries-td3867.html and was fix https://issues.apache.org/ > jira/browse/IGNITE-2208 which allows compare binary objects but I’m not > sure if this fix works for condition where we have <= , >=, < , >. > I don’t see any test in IgniteBinaryObjectQueryArgumentsTest. I have > checked GridH2ValueCacheObject method compareSecure and I don’t see support > for BinaryObject comparision. Also looke like was change in > GridCacheSqlQuery and method unmarshall was removed in version 2.0. What > can explain different behavior between version 1.9 and 2.1. > > My question is if is possible to do such query: "birthDate >= ? and > birthDate <= ?", where query argument is joda DateTime type? > > Regards, > Czeslaw > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > http://apache-ignite-users.70518.x6.nabble.com/Query-by- > java-joda-DateTime-tp15902.html > To start a new topic under Apache Ignite Users, email > [email protected] > To unsubscribe from Apache Ignite Users, click here > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=1&code=YWxleGFuZGVyLmZlZG90b2ZmQGdtYWlsLmNvbXwxfC0xMzYxNTU0NTg=> > . > NAML > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Query-by-java-joda-DateTime-tp15902p15912.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.
