You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Mark Fenbers - NOAA Federal <ma...@noaa.gov.INVALID> on 2019/06/07 12:02:13 UTC
searching only within a date range
Hello!
I have a search setup and it works fine. I search a text field called
"logtext" in a database table. My Java code is like this:
SolrQuery query - new SolrQuery();
query.setQuery(searchWord);
query.setParam("df", "logtext");
Then I execute the search... and it works just great. But now I want to
add a constraint to only search for the "searchWord" within a certain range
of time -- given timestamps in the column called "posttime". So, I added
the code in bold below:
SolrQuery query - new SolrQuery();
query.setQuery(searchWord);
*query.setFacet(true);*
*query.addDateRangeFacet("posttime", new Date(System.currentTimeMillis() -
1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY"); /*
from 1 year ago to present) */*
query.setParam("df", "logtext");
But this gives me a complaint: *undefined field: "posttime"* so I clearly
do not understand the arguments needed to addDateRangeFacet(). Can someone
help me determine the proper code for doing what I want?
Further, I am puzzled about the "gap" argument [last one in
addDateRangeFacet()]. What does this do? I used +1DAY, but I really have
no idea the purpose of this. I haven't found any documentation that
explains this well.
Mark
Re: searching only within a date range
Posted by Mark Fenbers - NOAA Federal <ma...@noaa.gov.INVALID>.
I added "posttime" to the schema first thing this morning, but your message
reminded me that I needed to re-index the table, which I did. My schema
entry:
<field name="id" type="string" indexed="true" stored="true" required="true"
multiValued="false"/>
<field name="posttime" type="date" indexed="true" stored="true"
required="true" multiValued="false"/>
But my SQL contains "SELECT posttime as id" as so I tried both "posttime"
and "id" in my setParam() function, namely,
query.setParam("fq", "id:[2007-01-01T00:00:00Z TO 2010-01-01T00:00:00Z]");
So, whether I use "id" (string) or "posttime" (date), my results are an
immediate return of zero results.
I did look in the admin interface and *did* see posttime listed as one of
the index items. The two rows (Index Analyzer and Query Analyzer) show the
same thing: org.apache.solr.schema.FieldType$DefaultAnalyzer, though I'm
not certain of the implications of this.
I have not attempted your &debug=query suggestion just yet...
Mark
On Fri, Jun 7, 2019 at 10:40 AM Erick Erickson <er...@gmail.com>
wrote:
> Yeah, it can be opaque…
>
> My first guess is that you may not have a field “posttime” defined in your
> schema and/or documents. For searching it needs “indexed=true” and for
> faceting/grouping/sorting it should have “docValues=true”. That’s what your
> original facet query was telling you, the field isn’t there. Switching to
> an “fq” clause is consistent with there being no “posttime” field since
> Solr is fine with docs that don’t have a particular field. So by
> specifying a date range, any doc without a “posttime” field will be omitted
> from the results.
>
> Or it just is spelled differently ;)
>
> Some things that might help:
>
> 1> Go to the admin UI and select cores>>your_core, then look at the
> “schema” link. There’s a drop-down that lets you select fields that are
> actually in your index and see some of the values. My bet: “posttime”
> isn’t in the list. If so, you need to add it and re-index the docs with a
> posttime field. If there is a “posttime”, select it and look at the upper
> right to see how it’s defined. There are two rows, one for what the schema
> thinks the definition is and one for what is actually in the Lucene index.
>
> 2> add &debug=query to your queries, and run them from the admin UI.
> That’ll give you a _lot_ quicker turn-around as well as some good info
> about how the query was actually executed.
>
> Best,
> Erick
>
> > On Jun 7, 2019, at 7:23 AM, Mark Fenbers - NOAA Federal
> <ma...@noaa.gov.INVALID> wrote:
> >
> > So, instead of addDateRangeFacet(), I used:
> > query.setParam("fq", "posttime:[2010-01-01T00:00:00Z TO
> > 2015-01-01T00:00:00Z]");
> >
> > I didn't get any errors, but the query returned immediately with 0
> > results. Without this contraint, it searches 13,000 records and takes 1
> to
> > 2 minutes and returns 356 records. So something is not quite right, and
> > I'm too new at this to understand where I went wrong.
> > Mark
> >
> > On Fri, Jun 7, 2019 at 9:52 AM Andrea Gazzarini <a....@sease.io>
> > wrote:
> >
> >> Hi Mark, you are using a "range facet" which is a "query-shape" feature,
> >> it doesn't have any constraint on the results (i.e. it doesn't filter at
> >> all).
> >> You need to add a filter query [1] with a date range clause (e.g.
> >> fq=field:[<some date or DateMath exp or *> TO <some date or DateMath exp
> >> or *>]).
> >>
> >> Best,
> >> Andrea
> >>
> >> [1]
> >>
> >>
> https://lucene.apache.org/solr/guide/6_6/common-query-parameters.html#CommonQueryParameters-Thefq_FilterQuery_Parameter
> >> [2] https://lucene.apache.org/solr/guide/6_6/working-with-dates.html
> >>
> >> On 07/06/2019 14:02, Mark Fenbers - NOAA Federal wrote:
> >>> Hello!
> >>>
> >>> I have a search setup and it works fine. I search a text field called
> >>> "logtext" in a database table. My Java code is like this:
> >>>
> >>> SolrQuery query - new SolrQuery();
> >>> query.setQuery(searchWord);
> >>> query.setParam("df", "logtext");
> >>>
> >>> Then I execute the search... and it works just great. But now I want
> to
> >>> add a constraint to only search for the "searchWord" within a certain
> >> range
> >>> of time -- given timestamps in the column called "posttime". So, I
> added
> >>> the code in bold below:
> >>>
> >>> SolrQuery query - new SolrQuery();
> >>> query.setQuery(searchWord);
> >>> *query.setFacet(true);*
> >>> *query.addDateRangeFacet("posttime", new
> Date(System.currentTimeMillis()
> >> -
> >>> 1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY");
> >> /*
> >>> from 1 year ago to present) */*
> >>> query.setParam("df", "logtext");
> >>>
> >>> But this gives me a complaint: *undefined field: "posttime"* so I
> clearly
> >>> do not understand the arguments needed to addDateRangeFacet(). Can
> >> someone
> >>> help me determine the proper code for doing what I want?
> >>>
> >>> Further, I am puzzled about the "gap" argument [last one in
> >>> addDateRangeFacet()]. What does this do? I used +1DAY, but I really
> >> have
> >>> no idea the purpose of this. I haven't found any documentation that
> >>> explains this well.
> >>>
> >>> Mark
> >>>
> >>
> >>
>
>
Re: searching only within a date range
Posted by Mark Fenbers - NOAA Federal <ma...@noaa.gov.INVALID>.
Disregard my previous response. When I reindexed, something went wrong and
so my Lucene database was empty, which explains the immediate results and 0
results. I reindexed again (properly) and all is working find now. Thanks
for the help.
Mark
On Fri, Jun 7, 2019 at 10:40 AM Erick Erickson <er...@gmail.com>
wrote:
> Yeah, it can be opaque…
>
> My first guess is that you may not have a field “posttime” defined in your
> schema and/or documents. For searching it needs “indexed=true” and for
> faceting/grouping/sorting it should have “docValues=true”. That’s what your
> original facet query was telling you, the field isn’t there. Switching to
> an “fq” clause is consistent with there being no “posttime” field since
> Solr is fine with docs that don’t have a particular field. So by
> specifying a date range, any doc without a “posttime” field will be omitted
> from the results.
>
> Or it just is spelled differently ;)
>
> Some things that might help:
>
> 1> Go to the admin UI and select cores>>your_core, then look at the
> “schema” link. There’s a drop-down that lets you select fields that are
> actually in your index and see some of the values. My bet: “posttime”
> isn’t in the list. If so, you need to add it and re-index the docs with a
> posttime field. If there is a “posttime”, select it and look at the upper
> right to see how it’s defined. There are two rows, one for what the schema
> thinks the definition is and one for what is actually in the Lucene index.
>
> 2> add &debug=query to your queries, and run them from the admin UI.
> That’ll give you a _lot_ quicker turn-around as well as some good info
> about how the query was actually executed.
>
> Best,
> Erick
>
> > On Jun 7, 2019, at 7:23 AM, Mark Fenbers - NOAA Federal
> <ma...@noaa.gov.INVALID> wrote:
> >
> > So, instead of addDateRangeFacet(), I used:
> > query.setParam("fq", "posttime:[2010-01-01T00:00:00Z TO
> > 2015-01-01T00:00:00Z]");
> >
> > I didn't get any errors, but the query returned immediately with 0
> > results. Without this contraint, it searches 13,000 records and takes 1
> to
> > 2 minutes and returns 356 records. So something is not quite right, and
> > I'm too new at this to understand where I went wrong.
> > Mark
> >
> > On Fri, Jun 7, 2019 at 9:52 AM Andrea Gazzarini <a....@sease.io>
> > wrote:
> >
> >> Hi Mark, you are using a "range facet" which is a "query-shape" feature,
> >> it doesn't have any constraint on the results (i.e. it doesn't filter at
> >> all).
> >> You need to add a filter query [1] with a date range clause (e.g.
> >> fq=field:[<some date or DateMath exp or *> TO <some date or DateMath exp
> >> or *>]).
> >>
> >> Best,
> >> Andrea
> >>
> >> [1]
> >>
> >>
> https://lucene.apache.org/solr/guide/6_6/common-query-parameters.html#CommonQueryParameters-Thefq_FilterQuery_Parameter
> >> [2] https://lucene.apache.org/solr/guide/6_6/working-with-dates.html
> >>
> >> On 07/06/2019 14:02, Mark Fenbers - NOAA Federal wrote:
> >>> Hello!
> >>>
> >>> I have a search setup and it works fine. I search a text field called
> >>> "logtext" in a database table. My Java code is like this:
> >>>
> >>> SolrQuery query - new SolrQuery();
> >>> query.setQuery(searchWord);
> >>> query.setParam("df", "logtext");
> >>>
> >>> Then I execute the search... and it works just great. But now I want
> to
> >>> add a constraint to only search for the "searchWord" within a certain
> >> range
> >>> of time -- given timestamps in the column called "posttime". So, I
> added
> >>> the code in bold below:
> >>>
> >>> SolrQuery query - new SolrQuery();
> >>> query.setQuery(searchWord);
> >>> *query.setFacet(true);*
> >>> *query.addDateRangeFacet("posttime", new
> Date(System.currentTimeMillis()
> >> -
> >>> 1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY");
> >> /*
> >>> from 1 year ago to present) */*
> >>> query.setParam("df", "logtext");
> >>>
> >>> But this gives me a complaint: *undefined field: "posttime"* so I
> clearly
> >>> do not understand the arguments needed to addDateRangeFacet(). Can
> >> someone
> >>> help me determine the proper code for doing what I want?
> >>>
> >>> Further, I am puzzled about the "gap" argument [last one in
> >>> addDateRangeFacet()]. What does this do? I used +1DAY, but I really
> >> have
> >>> no idea the purpose of this. I haven't found any documentation that
> >>> explains this well.
> >>>
> >>> Mark
> >>>
> >>
> >>
>
>
Re: searching only within a date range
Posted by Erick Erickson <er...@gmail.com>.
Yeah, it can be opaque…
My first guess is that you may not have a field “posttime” defined in your schema and/or documents. For searching it needs “indexed=true” and for faceting/grouping/sorting it should have “docValues=true”. That’s what your original facet query was telling you, the field isn’t there. Switching to an “fq” clause is consistent with there being no “posttime” field since Solr is fine with docs that don’t have a particular field. So by specifying a date range, any doc without a “posttime” field will be omitted from the results.
Or it just is spelled differently ;)
Some things that might help:
1> Go to the admin UI and select cores>>your_core, then look at the “schema” link. There’s a drop-down that lets you select fields that are actually in your index and see some of the values. My bet: “posttime” isn’t in the list. If so, you need to add it and re-index the docs with a posttime field. If there is a “posttime”, select it and look at the upper right to see how it’s defined. There are two rows, one for what the schema thinks the definition is and one for what is actually in the Lucene index.
2> add &debug=query to your queries, and run them from the admin UI. That’ll give you a _lot_ quicker turn-around as well as some good info about how the query was actually executed.
Best,
Erick
> On Jun 7, 2019, at 7:23 AM, Mark Fenbers - NOAA Federal <ma...@noaa.gov.INVALID> wrote:
>
> So, instead of addDateRangeFacet(), I used:
> query.setParam("fq", "posttime:[2010-01-01T00:00:00Z TO
> 2015-01-01T00:00:00Z]");
>
> I didn't get any errors, but the query returned immediately with 0
> results. Without this contraint, it searches 13,000 records and takes 1 to
> 2 minutes and returns 356 records. So something is not quite right, and
> I'm too new at this to understand where I went wrong.
> Mark
>
> On Fri, Jun 7, 2019 at 9:52 AM Andrea Gazzarini <a....@sease.io>
> wrote:
>
>> Hi Mark, you are using a "range facet" which is a "query-shape" feature,
>> it doesn't have any constraint on the results (i.e. it doesn't filter at
>> all).
>> You need to add a filter query [1] with a date range clause (e.g.
>> fq=field:[<some date or DateMath exp or *> TO <some date or DateMath exp
>> or *>]).
>>
>> Best,
>> Andrea
>>
>> [1]
>>
>> https://lucene.apache.org/solr/guide/6_6/common-query-parameters.html#CommonQueryParameters-Thefq_FilterQuery_Parameter
>> [2] https://lucene.apache.org/solr/guide/6_6/working-with-dates.html
>>
>> On 07/06/2019 14:02, Mark Fenbers - NOAA Federal wrote:
>>> Hello!
>>>
>>> I have a search setup and it works fine. I search a text field called
>>> "logtext" in a database table. My Java code is like this:
>>>
>>> SolrQuery query - new SolrQuery();
>>> query.setQuery(searchWord);
>>> query.setParam("df", "logtext");
>>>
>>> Then I execute the search... and it works just great. But now I want to
>>> add a constraint to only search for the "searchWord" within a certain
>> range
>>> of time -- given timestamps in the column called "posttime". So, I added
>>> the code in bold below:
>>>
>>> SolrQuery query - new SolrQuery();
>>> query.setQuery(searchWord);
>>> *query.setFacet(true);*
>>> *query.addDateRangeFacet("posttime", new Date(System.currentTimeMillis()
>> -
>>> 1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY");
>> /*
>>> from 1 year ago to present) */*
>>> query.setParam("df", "logtext");
>>>
>>> But this gives me a complaint: *undefined field: "posttime"* so I clearly
>>> do not understand the arguments needed to addDateRangeFacet(). Can
>> someone
>>> help me determine the proper code for doing what I want?
>>>
>>> Further, I am puzzled about the "gap" argument [last one in
>>> addDateRangeFacet()]. What does this do? I used +1DAY, but I really
>> have
>>> no idea the purpose of this. I haven't found any documentation that
>>> explains this well.
>>>
>>> Mark
>>>
>>
>>
Re: searching only within a date range
Posted by Mark Fenbers - NOAA Federal <ma...@noaa.gov.INVALID>.
So, instead of addDateRangeFacet(), I used:
query.setParam("fq", "posttime:[2010-01-01T00:00:00Z TO
2015-01-01T00:00:00Z]");
I didn't get any errors, but the query returned immediately with 0
results. Without this contraint, it searches 13,000 records and takes 1 to
2 minutes and returns 356 records. So something is not quite right, and
I'm too new at this to understand where I went wrong.
Mark
On Fri, Jun 7, 2019 at 9:52 AM Andrea Gazzarini <a....@sease.io>
wrote:
> Hi Mark, you are using a "range facet" which is a "query-shape" feature,
> it doesn't have any constraint on the results (i.e. it doesn't filter at
> all).
> You need to add a filter query [1] with a date range clause (e.g.
> fq=field:[<some date or DateMath exp or *> TO <some date or DateMath exp
> or *>]).
>
> Best,
> Andrea
>
> [1]
>
> https://lucene.apache.org/solr/guide/6_6/common-query-parameters.html#CommonQueryParameters-Thefq_FilterQuery_Parameter
> [2] https://lucene.apache.org/solr/guide/6_6/working-with-dates.html
>
> On 07/06/2019 14:02, Mark Fenbers - NOAA Federal wrote:
> > Hello!
> >
> > I have a search setup and it works fine. I search a text field called
> > "logtext" in a database table. My Java code is like this:
> >
> > SolrQuery query - new SolrQuery();
> > query.setQuery(searchWord);
> > query.setParam("df", "logtext");
> >
> > Then I execute the search... and it works just great. But now I want to
> > add a constraint to only search for the "searchWord" within a certain
> range
> > of time -- given timestamps in the column called "posttime". So, I added
> > the code in bold below:
> >
> > SolrQuery query - new SolrQuery();
> > query.setQuery(searchWord);
> > *query.setFacet(true);*
> > *query.addDateRangeFacet("posttime", new Date(System.currentTimeMillis()
> -
> > 1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY");
> /*
> > from 1 year ago to present) */*
> > query.setParam("df", "logtext");
> >
> > But this gives me a complaint: *undefined field: "posttime"* so I clearly
> > do not understand the arguments needed to addDateRangeFacet(). Can
> someone
> > help me determine the proper code for doing what I want?
> >
> > Further, I am puzzled about the "gap" argument [last one in
> > addDateRangeFacet()]. What does this do? I used +1DAY, but I really
> have
> > no idea the purpose of this. I haven't found any documentation that
> > explains this well.
> >
> > Mark
> >
>
>
Re: searching only within a date range
Posted by Andrea Gazzarini <a....@sease.io>.
Hi Mark, you are using a "range facet" which is a "query-shape" feature,
it doesn't have any constraint on the results (i.e. it doesn't filter at
all).
You need to add a filter query [1] with a date range clause (e.g.
fq=field:[<some date or DateMath exp or *> TO <some date or DateMath exp
or *>]).
Best,
Andrea
[1]
https://lucene.apache.org/solr/guide/6_6/common-query-parameters.html#CommonQueryParameters-Thefq_FilterQuery_Parameter
[2] https://lucene.apache.org/solr/guide/6_6/working-with-dates.html
On 07/06/2019 14:02, Mark Fenbers - NOAA Federal wrote:
> Hello!
>
> I have a search setup and it works fine. I search a text field called
> "logtext" in a database table. My Java code is like this:
>
> SolrQuery query - new SolrQuery();
> query.setQuery(searchWord);
> query.setParam("df", "logtext");
>
> Then I execute the search... and it works just great. But now I want to
> add a constraint to only search for the "searchWord" within a certain range
> of time -- given timestamps in the column called "posttime". So, I added
> the code in bold below:
>
> SolrQuery query - new SolrQuery();
> query.setQuery(searchWord);
> *query.setFacet(true);*
> *query.addDateRangeFacet("posttime", new Date(System.currentTimeMillis() -
> 1000L * 86400L * 365L), new Date(System.currentTimeMillis()), "+1DAY"); /*
> from 1 year ago to present) */*
> query.setParam("df", "logtext");
>
> But this gives me a complaint: *undefined field: "posttime"* so I clearly
> do not understand the arguments needed to addDateRangeFacet(). Can someone
> help me determine the proper code for doing what I want?
>
> Further, I am puzzled about the "gap" argument [last one in
> addDateRangeFacet()]. What does this do? I used +1DAY, but I really have
> no idea the purpose of this. I haven't found any documentation that
> explains this well.
>
> Mark
>