You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Warner Onstine <wa...@gmail.com> on 2011/02/06 22:57:08 UTC
date range querying
Ok, been banging my head against this for a while and having no luck
figuring out the logical way to do this (other than installing
ElasticSearch - which I'll be doing in the near future).
I have records that have startDate and endDate ranges. I want to query
the view based on today's date. Say I have a record that has a start
date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
which records fall within today's date.
In other words
1/31/2011 < 2/6/2011 < 2/13/2011
How in the world do I do that? I've been trying something like this:
var startDate = new Date(doc.startDate);
var endDate = new Date(doc.endDate);
var startYear = startDate.getFullYear();
var startMonth = startDate.getMonth();
var startDay = startDate.getDate();
var endYear = endDate.getFullYear();
var endMonth = endDate.getMonth();
var endDay = endDate.getDate();
emit([
startYear,
startMonth,
startDay,
endYear,
endMonth,
endDay
]);
But as soon as I add in the endYear variable it goes wonky and starts
returning records that don't match at all. Any help is greatly
appreciated.
-warner
Re: date range querying
Posted by Warner Onstine <wa...@gmail.com>.
Ahhh, that makes a whole lot more sense. I'll see if I can play with
that query some more today.
Thanks!
-warner
On Sat, Feb 12, 2011 at 8:00 AM, Robert Newson <ro...@gmail.com> wrote:
> day=1 day was a typo *and* pseudocode.
>
> I mean simply that you should increment 'day' by '1 whole day' in the loop.
>
> let's assume day is an integral value from some epoch (0 meaning jan 1
> 1970, 1 meaning jan 2 1970, and so on).
>
> for (day=startDay; day<endDay; day++) {
> emit(day, null);
> }
>
> B.
>
> On 12 February 2011 14:46, Warner Onstine <wa...@gmail.com> wrote:
>> I tried that with no success (in fact it completely borked all views
>> for that document type and had to delete it to get things working
>> again. I may try and monkey around with these two scripts again to see
>> what I did wrong.
>>
>> It really didn't like
>> day= 1 day
>>
>> and honestly I'm not even sure what that's supposed to do.
>>
>> Thanks for the suggestions, I'll let you know if I figure it out. But
>> I am seriously thinking about using ElasticSearch to make it a more
>> robust searching solution (I have a lot of searching coming up soon).
>>
>> -warner
>>
>> On Sun, Feb 6, 2011 at 3:37 PM, Robert Newson <ro...@gmail.com> wrote:
>>> Within couchdb itself, I think you'd have to do as Nils suggested;
>>>
>>> for (day = startDate; day < endDate; day= 1 day) {
>>> emit(day, null);
>>> }
>>>
>>> then ?key=<day I want>
>>>
>>> Or you could use couchdb-lucene, index startDate and endDate as dates,
>>> query with ?q=startDate:[day TO maxdate]&endDate:[mindate TO day]
>>> where 'date' is the date you are searching for.
>>>
>>> B.
>>>
>>> On Sun, Feb 6, 2011 at 10:31 PM, Nils Breunese <N....@vpro.nl> wrote:
>>>> You could emit a view index entry for every day in the date range of a document? This could become inefficient storage-wise if you have large date ranges.
>>>>
>>>> What exactly "starts returning records that don't match at all"? In what way are you querying your view? I have to admit I have no idea how I could get the information you're looking for from that complex key. Also, the emit method takes two arguments AFAIK: a key and a value. You seem to be emitting a single value?
>>>>
>>>> Nils.
>>>> ________________________________________
>>>> Van: Warner Onstine [warnero@gmail.com]
>>>> Verzonden: zondag 6 februari 2011 22:57
>>>> Aan: user@couchdb.apache.org
>>>> Onderwerp: date range querying
>>>>
>>>> Ok, been banging my head against this for a while and having no luck
>>>> figuring out the logical way to do this (other than installing
>>>> ElasticSearch - which I'll be doing in the near future).
>>>>
>>>> I have records that have startDate and endDate ranges. I want to query
>>>> the view based on today's date. Say I have a record that has a start
>>>> date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
>>>> which records fall within today's date.
>>>>
>>>> In other words
>>>> 1/31/2011 < 2/6/2011 < 2/13/2011
>>>>
>>>> How in the world do I do that? I've been trying something like this:
>>>> var startDate = new Date(doc.startDate);
>>>> var endDate = new Date(doc.endDate);
>>>> var startYear = startDate.getFullYear();
>>>> var startMonth = startDate.getMonth();
>>>> var startDay = startDate.getDate();
>>>> var endYear = endDate.getFullYear();
>>>> var endMonth = endDate.getMonth();
>>>> var endDay = endDate.getDate();
>>>> emit([
>>>> startYear,
>>>> startMonth,
>>>> startDay,
>>>> endYear,
>>>> endMonth,
>>>> endDay
>>>> ]);
>>>>
>>>> But as soon as I add in the endYear variable it goes wonky and starts
>>>> returning records that don't match at all. Any help is greatly
>>>> appreciated.
>>>>
>>>> -warner
>>>> ------------------------------------------------------------------------
>>>> VPRO www.vpro.nl
>>>> ------------------------------------------------------------------------
>>>>
>>>
>>
>
Re: date range querying
Posted by Robert Newson <ro...@gmail.com>.
day=1 day was a typo *and* pseudocode.
I mean simply that you should increment 'day' by '1 whole day' in the loop.
let's assume day is an integral value from some epoch (0 meaning jan 1
1970, 1 meaning jan 2 1970, and so on).
for (day=startDay; day<endDay; day++) {
emit(day, null);
}
B.
On 12 February 2011 14:46, Warner Onstine <wa...@gmail.com> wrote:
> I tried that with no success (in fact it completely borked all views
> for that document type and had to delete it to get things working
> again. I may try and monkey around with these two scripts again to see
> what I did wrong.
>
> It really didn't like
> day= 1 day
>
> and honestly I'm not even sure what that's supposed to do.
>
> Thanks for the suggestions, I'll let you know if I figure it out. But
> I am seriously thinking about using ElasticSearch to make it a more
> robust searching solution (I have a lot of searching coming up soon).
>
> -warner
>
> On Sun, Feb 6, 2011 at 3:37 PM, Robert Newson <ro...@gmail.com> wrote:
>> Within couchdb itself, I think you'd have to do as Nils suggested;
>>
>> for (day = startDate; day < endDate; day= 1 day) {
>> emit(day, null);
>> }
>>
>> then ?key=<day I want>
>>
>> Or you could use couchdb-lucene, index startDate and endDate as dates,
>> query with ?q=startDate:[day TO maxdate]&endDate:[mindate TO day]
>> where 'date' is the date you are searching for.
>>
>> B.
>>
>> On Sun, Feb 6, 2011 at 10:31 PM, Nils Breunese <N....@vpro.nl> wrote:
>>> You could emit a view index entry for every day in the date range of a document? This could become inefficient storage-wise if you have large date ranges.
>>>
>>> What exactly "starts returning records that don't match at all"? In what way are you querying your view? I have to admit I have no idea how I could get the information you're looking for from that complex key. Also, the emit method takes two arguments AFAIK: a key and a value. You seem to be emitting a single value?
>>>
>>> Nils.
>>> ________________________________________
>>> Van: Warner Onstine [warnero@gmail.com]
>>> Verzonden: zondag 6 februari 2011 22:57
>>> Aan: user@couchdb.apache.org
>>> Onderwerp: date range querying
>>>
>>> Ok, been banging my head against this for a while and having no luck
>>> figuring out the logical way to do this (other than installing
>>> ElasticSearch - which I'll be doing in the near future).
>>>
>>> I have records that have startDate and endDate ranges. I want to query
>>> the view based on today's date. Say I have a record that has a start
>>> date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
>>> which records fall within today's date.
>>>
>>> In other words
>>> 1/31/2011 < 2/6/2011 < 2/13/2011
>>>
>>> How in the world do I do that? I've been trying something like this:
>>> var startDate = new Date(doc.startDate);
>>> var endDate = new Date(doc.endDate);
>>> var startYear = startDate.getFullYear();
>>> var startMonth = startDate.getMonth();
>>> var startDay = startDate.getDate();
>>> var endYear = endDate.getFullYear();
>>> var endMonth = endDate.getMonth();
>>> var endDay = endDate.getDate();
>>> emit([
>>> startYear,
>>> startMonth,
>>> startDay,
>>> endYear,
>>> endMonth,
>>> endDay
>>> ]);
>>>
>>> But as soon as I add in the endYear variable it goes wonky and starts
>>> returning records that don't match at all. Any help is greatly
>>> appreciated.
>>>
>>> -warner
>>> ------------------------------------------------------------------------
>>> VPRO www.vpro.nl
>>> ------------------------------------------------------------------------
>>>
>>
>
Re: date range querying
Posted by Warner Onstine <wa...@gmail.com>.
I tried that with no success (in fact it completely borked all views
for that document type and had to delete it to get things working
again. I may try and monkey around with these two scripts again to see
what I did wrong.
It really didn't like
day= 1 day
and honestly I'm not even sure what that's supposed to do.
Thanks for the suggestions, I'll let you know if I figure it out. But
I am seriously thinking about using ElasticSearch to make it a more
robust searching solution (I have a lot of searching coming up soon).
-warner
On Sun, Feb 6, 2011 at 3:37 PM, Robert Newson <ro...@gmail.com> wrote:
> Within couchdb itself, I think you'd have to do as Nils suggested;
>
> for (day = startDate; day < endDate; day= 1 day) {
> emit(day, null);
> }
>
> then ?key=<day I want>
>
> Or you could use couchdb-lucene, index startDate and endDate as dates,
> query with ?q=startDate:[day TO maxdate]&endDate:[mindate TO day]
> where 'date' is the date you are searching for.
>
> B.
>
> On Sun, Feb 6, 2011 at 10:31 PM, Nils Breunese <N....@vpro.nl> wrote:
>> You could emit a view index entry for every day in the date range of a document? This could become inefficient storage-wise if you have large date ranges.
>>
>> What exactly "starts returning records that don't match at all"? In what way are you querying your view? I have to admit I have no idea how I could get the information you're looking for from that complex key. Also, the emit method takes two arguments AFAIK: a key and a value. You seem to be emitting a single value?
>>
>> Nils.
>> ________________________________________
>> Van: Warner Onstine [warnero@gmail.com]
>> Verzonden: zondag 6 februari 2011 22:57
>> Aan: user@couchdb.apache.org
>> Onderwerp: date range querying
>>
>> Ok, been banging my head against this for a while and having no luck
>> figuring out the logical way to do this (other than installing
>> ElasticSearch - which I'll be doing in the near future).
>>
>> I have records that have startDate and endDate ranges. I want to query
>> the view based on today's date. Say I have a record that has a start
>> date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
>> which records fall within today's date.
>>
>> In other words
>> 1/31/2011 < 2/6/2011 < 2/13/2011
>>
>> How in the world do I do that? I've been trying something like this:
>> var startDate = new Date(doc.startDate);
>> var endDate = new Date(doc.endDate);
>> var startYear = startDate.getFullYear();
>> var startMonth = startDate.getMonth();
>> var startDay = startDate.getDate();
>> var endYear = endDate.getFullYear();
>> var endMonth = endDate.getMonth();
>> var endDay = endDate.getDate();
>> emit([
>> startYear,
>> startMonth,
>> startDay,
>> endYear,
>> endMonth,
>> endDay
>> ]);
>>
>> But as soon as I add in the endYear variable it goes wonky and starts
>> returning records that don't match at all. Any help is greatly
>> appreciated.
>>
>> -warner
>> ------------------------------------------------------------------------
>> VPRO www.vpro.nl
>> ------------------------------------------------------------------------
>>
>
Re: date range querying
Posted by Robert Newson <ro...@gmail.com>.
Within couchdb itself, I think you'd have to do as Nils suggested;
for (day = startDate; day < endDate; day= 1 day) {
emit(day, null);
}
then ?key=<day I want>
Or you could use couchdb-lucene, index startDate and endDate as dates,
query with ?q=startDate:[day TO maxdate]&endDate:[mindate TO day]
where 'date' is the date you are searching for.
B.
On Sun, Feb 6, 2011 at 10:31 PM, Nils Breunese <N....@vpro.nl> wrote:
> You could emit a view index entry for every day in the date range of a document? This could become inefficient storage-wise if you have large date ranges.
>
> What exactly "starts returning records that don't match at all"? In what way are you querying your view? I have to admit I have no idea how I could get the information you're looking for from that complex key. Also, the emit method takes two arguments AFAIK: a key and a value. You seem to be emitting a single value?
>
> Nils.
> ________________________________________
> Van: Warner Onstine [warnero@gmail.com]
> Verzonden: zondag 6 februari 2011 22:57
> Aan: user@couchdb.apache.org
> Onderwerp: date range querying
>
> Ok, been banging my head against this for a while and having no luck
> figuring out the logical way to do this (other than installing
> ElasticSearch - which I'll be doing in the near future).
>
> I have records that have startDate and endDate ranges. I want to query
> the view based on today's date. Say I have a record that has a start
> date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
> which records fall within today's date.
>
> In other words
> 1/31/2011 < 2/6/2011 < 2/13/2011
>
> How in the world do I do that? I've been trying something like this:
> var startDate = new Date(doc.startDate);
> var endDate = new Date(doc.endDate);
> var startYear = startDate.getFullYear();
> var startMonth = startDate.getMonth();
> var startDay = startDate.getDate();
> var endYear = endDate.getFullYear();
> var endMonth = endDate.getMonth();
> var endDay = endDate.getDate();
> emit([
> startYear,
> startMonth,
> startDay,
> endYear,
> endMonth,
> endDay
> ]);
>
> But as soon as I add in the endYear variable it goes wonky and starts
> returning records that don't match at all. Any help is greatly
> appreciated.
>
> -warner
> ------------------------------------------------------------------------
> VPRO www.vpro.nl
> ------------------------------------------------------------------------
>
RE: date range querying
Posted by Nils Breunese <N....@vpro.nl>.
You could emit a view index entry for every day in the date range of a document? This could become inefficient storage-wise if you have large date ranges.
What exactly "starts returning records that don't match at all"? In what way are you querying your view? I have to admit I have no idea how I could get the information you're looking for from that complex key. Also, the emit method takes two arguments AFAIK: a key and a value. You seem to be emitting a single value?
Nils.
________________________________________
Van: Warner Onstine [warnero@gmail.com]
Verzonden: zondag 6 februari 2011 22:57
Aan: user@couchdb.apache.org
Onderwerp: date range querying
Ok, been banging my head against this for a while and having no luck
figuring out the logical way to do this (other than installing
ElasticSearch - which I'll be doing in the near future).
I have records that have startDate and endDate ranges. I want to query
the view based on today's date. Say I have a record that has a start
date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
which records fall within today's date.
In other words
1/31/2011 < 2/6/2011 < 2/13/2011
How in the world do I do that? I've been trying something like this:
var startDate = new Date(doc.startDate);
var endDate = new Date(doc.endDate);
var startYear = startDate.getFullYear();
var startMonth = startDate.getMonth();
var startDay = startDate.getDate();
var endYear = endDate.getFullYear();
var endMonth = endDate.getMonth();
var endDay = endDate.getDate();
emit([
startYear,
startMonth,
startDay,
endYear,
endMonth,
endDay
]);
But as soon as I add in the endYear variable it goes wonky and starts
returning records that don't match at all. Any help is greatly
appreciated.
-warner
------------------------------------------------------------------------
VPRO www.vpro.nl
------------------------------------------------------------------------
Re: date range querying
Posted by Gabriel Farrell <gs...@gmail.com>.
Could you split this into two queries? That is, run one view that
emits startDate for each record and one that emits endDate, then run a
query for startDate < today and another for endDate > today, then
calculate the intersection of the two sets in your app?
On Sun, Feb 6, 2011 at 4:57 PM, Warner Onstine <wa...@gmail.com> wrote:
> Ok, been banging my head against this for a while and having no luck
> figuring out the logical way to do this (other than installing
> ElasticSearch - which I'll be doing in the near future).
>
> I have records that have startDate and endDate ranges. I want to query
> the view based on today's date. Say I have a record that has a start
> date of 1/31/2011 and an end date of 2/13/2011 and I want to find out
> which records fall within today's date.
>
> In other words
> 1/31/2011 < 2/6/2011 < 2/13/2011
>
> How in the world do I do that? I've been trying something like this:
> var startDate = new Date(doc.startDate);
> var endDate = new Date(doc.endDate);
> var startYear = startDate.getFullYear();
> var startMonth = startDate.getMonth();
> var startDay = startDate.getDate();
> var endYear = endDate.getFullYear();
> var endMonth = endDate.getMonth();
> var endDay = endDate.getDate();
> emit([
> startYear,
> startMonth,
> startDay,
> endYear,
> endMonth,
> endDay
> ]);
>
> But as soon as I add in the endYear variable it goes wonky and starts
> returning records that don't match at all. Any help is greatly
> appreciated.
>
> -warner
>