Solr8.0.0 date search issue

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Solr8.0.0 date search issue

Anuj Bhargava
I have an issue while searching on the Date field date_upload

My Schema file has the following entry for DATE Field

*<field name="date_upload" type="pdate" indexed="true" stored="true"/>*

My data-config.xml has the following entry -
























*<dataConfig><dataSource type="JdbcDataSource"
driver="com.mysql.jdbc.Driver"            batchSize="-1"
autoReconnect="true"            socketTimeout="0"
connectTimeout="0"            encoding="UTF-8"
url="jdbc:mysql://xxx.xxx.xxx.xx:3306/news?zeroDateTimeBehavior=convertToNull"
user="admin"            password="admin"/><document><!--<entity
name="news10" query="select * from news10"            deltaQuery="select
posting_id from item where last_modified >
'${dataimporter.last_index_time}'"> </entity>--><entity name="news10"
pk="posting_id"  query="SELECT * FROM news10"  deltaImportQuery="SELECT *
FROM news10    WHERE poting_id = '${dataimporter.delta.posting_id}'"
deltaQuery="SELECT posting_id FROM news10    WHERE last_modified >
'${dataimporter.last_index_time}'"></entity></document></dataConfig>*

*The following do not work -*
fq=date_upload:NOW  (does not work)
http://localhost:8983/solr/Nlive/select?fq=date_c%3ANOW&q=*%3A*

fq=date_upload:NOW-1DAY  (does not work)
fq=date_upload:(NOW-30DAYS)  (does not work)

fq=date_upload:[2018-12-01T:00:00:00Z TO 2019-04-17T00:00:00Z]  (does not
work)
*"msg":"Invalid Date in Date Math String:'2018-12-01T:00:00:00Z'",*

fq=date_upload:[2018-12-01 TO 2019-04-17] Gives the following error













*{  "responseHeader":{    "status":400,    "QTime":1,    "params":{
"q":"*:*",      "fq":"date_upload:[2018-12-01 TO 2019-04-17]",
"_":"1555386354522"}},  "error":{    "metadata":[
"error-class","org.apache.solr.common.SolrException",
"root-error-class","org.apache.solr.common.SolrException"],
"msg":"Invalid Date String:'2018-12-01'",    "code":400}}*

*However this does give results -*
fq=date_upload:[* TO NOW]
http://localhost:8983/solr/Nlive/select??fq=date_c%3A%5B*%20TO%20NOW%5D&q=*%3A*
Reply | Threaded
Open this post in threaded view
|

Re: Solr8.0.0 date search issue

Anuj Bhargava
Added the following in the Schema file -
<fieldType name="date_range" class="solr.DateRangeField"/>

And modified
<field name="date_upload" indexed="true" stored="true" multiValued="true" />
to
<field name="date_upload" type="date_range" indexed="true" stored="true"
multiValued="true" />

Still the following not working
NOW
NOW-7DAYS
NOW-30DAYS

The following has started working
fq=date_upload:[2018-12-01 TO 2019-04-17]

On Thu, 18 Apr 2019 at 08:15, Anuj Bhargava <[hidden email]> wrote:

> I have an issue while searching on the Date field date_upload
>
> My Schema file has the following entry for DATE Field
>
> *<field name="date_upload" type="pdate" indexed="true" stored="true"/>*
>
> My data-config.xml has the following entry -
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *<dataConfig><dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"            batchSize="-1"
> autoReconnect="true"            socketTimeout="0"
> connectTimeout="0"            encoding="UTF-8"
> url="jdbc:mysql://xxx.xxx.xxx.xx:3306/news?zeroDateTimeBehavior=convertToNull"
> user="admin"            password="admin"/><document><!--<entity
> name="news10" query="select * from news10"            deltaQuery="select
> posting_id from item where last_modified >
> '${dataimporter.last_index_time}'"> </entity>--><entity name="news10"
> pk="posting_id"  query="SELECT * FROM news10"  deltaImportQuery="SELECT *
> FROM news10    WHERE poting_id = '${dataimporter.delta.posting_id}'"
> deltaQuery="SELECT posting_id FROM news10    WHERE last_modified >
> '${dataimporter.last_index_time}'"></entity></document></dataConfig>*
>
> *The following do not work -*
> fq=date_upload:NOW  (does not work)
> http://localhost:8983/solr/Nlive/select?fq=date_c%3ANOW&q=*%3A*
>
> fq=date_upload:NOW-1DAY  (does not work)
> fq=date_upload:(NOW-30DAYS)  (does not work)
>
> fq=date_upload:[2018-12-01T:00:00:00Z TO 2019-04-17T00:00:00Z]  (does not
> work)
> *"msg":"Invalid Date in Date Math String:'2018-12-01T:00:00:00Z'",*
>
> fq=date_upload:[2018-12-01 TO 2019-04-17] Gives the following error
>
>
>
>
>
>
>
>
>
>
>
>
>
> *{  "responseHeader":{    "status":400,    "QTime":1,    "params":{
> "q":"*:*",      "fq":"date_upload:[2018-12-01 TO 2019-04-17]",
> "_":"1555386354522"}},  "error":{    "metadata":[
> "error-class","org.apache.solr.common.SolrException",
> "root-error-class","org.apache.solr.common.SolrException"],
> "msg":"Invalid Date String:'2018-12-01'",    "code":400}}*
>
> *However this does give results -*
> fq=date_upload:[* TO NOW]
>
> http://localhost:8983/solr/Nlive/select??fq=date_c%3A%5B*%20TO%20NOW%5D&q=*%3A*
>
Reply | Threaded
Open this post in threaded view
|

Re: Solr8.0.0 date search issue

Shawn Heisey-2
In reply to this post by Anuj Bhargava
On 4/17/2019 8:45 PM, Anuj Bhargava wrote:
> I have an issue while searching on the Date field date_upload
>
> My Schema file has the following entry for DATE Field
>
> *<field name="date_upload" type="pdate" indexed="true" stored="true"/>*

You haven't shown us the definition for pdate.

If it is what the Solr examples have, then it is a DatePointField.

> My data-config.xml has the following entry -

My assumption would be that in mysql, fields like date_upload are mysql
date fields with timestamp.  If they are date-only fields in mysql, then
DatePointField might not work - you would need to use DateRangeField as
the class, which you changed it to in your followup message.

> *The following do not work -*
> fq=date_upload:NOW  (does not work)
> http://localhost:8983/solr/Nlive/select?fq=date_c%3ANOW&q=*%3A*
>
> fq=date_upload:NOW-1DAY  (does not work)
> fq=date_upload:(NOW-30DAYS)  (does not work)

What *EXACTLY* do you mean when you say "does not work"?  One thing I
can tell you is that using NOW in this way means that you are searching
for an exact time -- to the millisecond.  If that exact time is not
found in the field, then you will get no results ... and chances are
that the exact time returned by any of these three queries does NOT
exist in the field.  Results would be the same with DateRangeField as
with DatePointField with these types of queries.

> fq=date_upload:[2018-12-01T:00:00:00Z TO 2019-04-17T00:00:00Z]  (does not
> work)
> *"msg":"Invalid Date in Date Math String:'2018-12-01T:00:00:00Z'",*

I would expect this to work.  The message is very odd ... I wonder if
any of the characters that were sent are not the actual ascii characters
but some kind of unicode character that looks the same but isn't the
right one.

> fq=date_upload:[2018-12-01 TO 2019-04-17] Gives the following error

Indeed this kind of query will not work on DatePointField.  But it will
work on DateRangeField.

> *However this does give results -*
> fq=date_upload:[* TO NOW]
> http://localhost:8983/solr/Nlive/select??fq=date_c%3A%5B*%20TO%20NOW%5D&q=*%3A*

I would expect this query to work on both field types.

Thanks,
Shawn