How do I compare dates in solr json

Question

I have an indexed document in solr:

{
        "id":"/content/dam/enron-cmi/fragments/headline-header/industry/industry commitment section header",
        "path":["/content/dam/enron-cmi/fragments/headline-header/industry/industry commitment section header"],
        "title":["Industry Commitment Section Header"],
        "previewText":["Industry commitment"],
        "publishedDate_dt":"2018-09-08T00:00:00Z",
        "lastModifiedDate_dt":"2018-09-08T00:00:00Z",
        "_version_":1624706590447763456},
      {
        "id":"/content/dam/enron-cmi/fragments/industry-overview/financial-services-overview---long",
        "path":["/content/dam/enron-cmi/fragments/industry-overview/financial-services-overview---long"],
        "title":["Financial Services Overview - Long"],
        "previewText":["Adaptation to the disruptive innovations that are driving necessary transformation and reform has me ..."],
        "publishedDate_dt":"2018-09-08T00:00:00Z",
        "lastModifiedDate_dt":"2018-09-08T00:00:00Z",
        "_version_":1624706590447763456}
        }

and my SolrInputDocument code looks like this(Index only when it is a valid date after converting to specific date):

if (tempObject.has("expirationDate") && tempObject.get("expirationDate") != null
                    && CommonUtils.isDateValid(tempObject.get("expirationDate").getAsString())) {
                nestedDoc.addField("expirationDate_dt",
                        CommonUtils.toUtcDate(tempObject.get("expirationDate").getAsString()));
            }

            if (tempObject.has("publishedDate") && tempObject.get("publishedDate") != null
                    && CommonUtils.isDateValid(tempObject.get("publishedDate").getAsString())) {
                nestedDoc.addField("publishedDate_dt",
                        CommonUtils.toUtcDate(tempObject.get("publishedDate").getAsString()));
            }

            if (tempObject.has("lastModifiedDate") && tempObject.get("lastModifiedDate") != null
                    && CommonUtils.isDateValid(tempObject.get("lastModifiedDate").getAsString())) {
                nestedDoc.addField("lastModifiedDate_dt",
                        CommonUtils.toUtcDate(tempObject.get("lastModifiedDate").getAsString()));
            }

Date Conversion util is as follows:

public static String toUtcDate(String dateStr) {
        SimpleDateFormat out = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
        String outDateFormat = StringUtils.EMPTY;
        String dateFormat = "MM-dd-yyyy";

        try {
            outDateFormat = out.format(new SimpleDateFormat(dateFormat).parse(dateStr));
        } catch (Exception ignore) {
            LOG.info(ignore.getMessage(), null, ignore);
        }

        return outDateFormat;
    }

After I index my content, I want to query for a date comparison for example: Display only for publishedDate_dt > lastModifiedDate_dt how can I do it ? The documents for solr is not really clear. Solr version is 7.6.

Can I use solr java api to do the same ?

I have tried the following query:

http://localhost:8983/solr/collection/select?fl=publishedDate_dt,lastModifiedDate_dt&defType=func&q=ms(publishedDate_dt,%20lastModifiedDate_dt)

and the output is :

"docs": [ { "publishedDate_dt": "2018-09-20T00:00:00Z" }, { "publishedDate_dt": "2018-09-01T00:00:00Z" }, { "publishedDate_dt": "2018-09-01T00:00:00Z" }, { "publishedDate_dt": "2018-09-01T00:00:00Z" },

tried this out:

http://localhost:8983/solr/collection/select?&q=:&fl=expirationDate_dt,publishedDate_dt,ms(expirationDate_dt,publishedDate_dt)&defType=func output is:

{
"responseHeader": {
"zkConnected": true,
"status": 400,
"QTime": 2,
"params": {
"q": "*:*",
"defType": "func",
"fl": "expirationDate_dt,publishedDate_dt,ms(expirationDate_dt,publishedDate_dt)"
}
},
"error": {
"metadata": [
"error-class",
"org.apache.solr.common.SolrException",
"root-error-class",
"org.apache.solr.search.SyntaxError"
],
"msg": "org.apache.solr.search.SyntaxError: Expected identifier at pos 0 str='*:*'",
"code": 400
}
}

what am I doing wrong ?

Abhijit Bashetti Abhijit Bashetti · Accepted Answer · 2019-02-06T09:40:25

In this case you can use the Function Queries.

Here is the link for it Function Queries & Available Functions

You can use the ms Function : It returns milliseconds of difference between its arguments

Syntax Examples

ms(NOW/DAY)
ms(2000-01-01T00:00:00Z)
ms(mydatefield)
ms(NOW,mydatefield)
ms(mydatefield, 2000-01-01T00:00:00Z)
ms(datefield1, datefield2)

How do I compare dates in solr json

1 Answers