Solr Range Queries with Field value

[Indika Tantrigoda <in...@gmail.com>]

Hi All,

I'd like to know if its possible to use a field value in a Solr range query
? Something similar to start_time_utc_epoch:[1970-01-01T00:00:00Z TO
merchant_end_of_day_in_utc_epoch])

merchant_end_of_day_in_utc_epoch is an indexed field.

I am using Solr 4.

Thanks,
Indika

Re: Solr Range Queries with Field value

[Chris Hostetter <ho...@fucit.org>]

: After some research the following syntax worked
: start_time_utc_epoch:[1970-01-01T00:00:00Z TO
: _val_:"merchant_end_of_day_in_utc_epoch"])

that syntax definitely does not work ... i don't know if there is a typo 
in your mail, or if you are just getting strange results that happen to 
look like they might be correct, but that syntax is not doing what you 
asked for.

the most straight forward way to achieve what you described is by using 
the "frange" parser to filter on the output of a funcion where the inputs 
are your two filed names...

  fq={!frange l=0}sub(merchant_end_of_day_in_utc_epoch,start_time_utc_epoch)

  https://wiki.apache.org/solr/FunctionQuery#Using_FunctionQuery

...that filter query will only match documents where the value of the 
field merchant_end_of_day_in_utc_epoch minus the value of the field 
start_time_utc_epoch is greater then zero.

if you want to speed that up, just do the calculation when indexing and 
create a special boolean field to filter on...

  fq=merchant_end_of_day_after_start_time:true

...you can then combine that with another filter on the date range...

  fq=start_time:[1970-01-01T00:00:00Z TO *]

...or you can combine everything into one function using the "ms" function 
to convert dates to numeric milliseconds

: > I believe Indika wants to do this SQL WHERE clause in Solr:
: > WHERE start_time_utc_epoch >= '1970-01-01T00:00:00Z' AND
: > start_time_utc_epoch
: > <= merchant_end_of_day_in_utc_epoch

-Hoss

Re: Solr Range Queries with Field value

[Indika Tantrigoda <in...@gmail.com>]

Yes, the SQL statement is what I am trying to achieve. As for the
merchant_end_of_day_in_utc_epoch, we map the time to start of epoch and
convert that to UTC, so that all the merchants are in the same timezone
which would make it easier to query for open ones.

For the use case when we need to determine if a merchant is currently open
now or in the future (within the same day). Therefore when converting the
start/end times to epoch and UTC a session (i.e. start time to end time)
might get spilled over past 12 midnight.

After some research the following syntax worked
start_time_utc_epoch:[1970-01-01T00:00:00Z TO
_val_:"merchant_end_of_day_in_utc_epoch"])

Thanks,
Indika



On 1 May 2013 00:52, Arun Rangarajan <ar...@gmail.com> wrote:

> Erick,
>
> I believe Indika wants to do this SQL WHERE clause in Solr:
> WHERE start_time_utc_epoch >= '1970-01-01T00:00:00Z' AND
> start_time_utc_epoch
> <= merchant_end_of_day_in_utc_epoch
>
>
> On Tue, Apr 30, 2013 at 11:49 AM, Erick Erickson <erickerickson@gmail.com
> >wrote:
>
> > Could you define your use-case in some more detail? On the
> > surface, this query doesn't really make a lot of sense. How
> > would merchant_end_of_day_in_utc_epoch be determined?
> > Presumably there are zillions of values across your index for
> > this value, depending on the document. Which one should be
> > used?
> >
> > Best
> > Erick
> >
> > On Mon, Apr 29, 2013 at 8:34 PM, Indika Tantrigoda <in...@gmail.com>
> > wrote:
> > > Hi All,
> > >
> > > I'd like to know if its possible to use a field value in a Solr range
> > query
> > > ? Something similar to start_time_utc_epoch:[1970-01-01T00:00:00Z TO
> > > merchant_end_of_day_in_utc_epoch])
> > >
> > > merchant_end_of_day_in_utc_epoch is an indexed field.
> > >
> > > I am using Solr 4.
> > >
> > > Thanks,
> > > Indika
> >
>

Re: Solr Range Queries with Field value

[Arun Rangarajan <ar...@gmail.com>]

Erick,

I believe Indika wants to do this SQL WHERE clause in Solr:
WHERE start_time_utc_epoch >= '1970-01-01T00:00:00Z' AND start_time_utc_epoch
<= merchant_end_of_day_in_utc_epoch


On Tue, Apr 30, 2013 at 11:49 AM, Erick Erickson <er...@gmail.com>wrote:

> Could you define your use-case in some more detail? On the
> surface, this query doesn't really make a lot of sense. How
> would merchant_end_of_day_in_utc_epoch be determined?
> Presumably there are zillions of values across your index for
> this value, depending on the document. Which one should be
> used?
>
> Best
> Erick
>
> On Mon, Apr 29, 2013 at 8:34 PM, Indika Tantrigoda <in...@gmail.com>
> wrote:
> > Hi All,
> >
> > I'd like to know if its possible to use a field value in a Solr range
> query
> > ? Something similar to start_time_utc_epoch:[1970-01-01T00:00:00Z TO
> > merchant_end_of_day_in_utc_epoch])
> >
> > merchant_end_of_day_in_utc_epoch is an indexed field.
> >
> > I am using Solr 4.
> >
> > Thanks,
> > Indika
>

Re: Solr Range Queries with Field value

[Erick Erickson <er...@gmail.com>]

Could you define your use-case in some more detail? On the
surface, this query doesn't really make a lot of sense. How
would merchant_end_of_day_in_utc_epoch be determined?
Presumably there are zillions of values across your index for
this value, depending on the document. Which one should be
used?

Best
Erick

On Mon, Apr 29, 2013 at 8:34 PM, Indika Tantrigoda <in...@gmail.com> wrote:
> Hi All,
>
> I'd like to know if its possible to use a field value in a Solr range query
> ? Something similar to start_time_utc_epoch:[1970-01-01T00:00:00Z TO
> merchant_end_of_day_in_utc_epoch])
>
> merchant_end_of_day_in_utc_epoch is an indexed field.
>
> I am using Solr 4.
>
> Thanks,
> Indika