Ah - that makes sense. Sphinx treats timestamps as unsigned 32-bit integers, starting from the epoch (1/1/1970) - and 200912 is almost certainly beyond that range ;)
Great to know it's all working properly now. -- Pat On 16/10/2010, at 5:34 AM, JeffLuckett wrote: > I think I've solved the issue. > > It turns out we had some crazy future dates in the database. I guess > Sphinx just throws up its hands (...er paws?) at dates sufficiently > far in the future. Surprised Postgres was happy with them. > > For example "200912-06-27 00:00:00" was one of them.. > > I cleaned out everything over 10 years in the future, and anything > before our company was founded from the database, and Sphinx is > spitting out believable results now. > > On Oct 15, 10:24 am, JeffLuckett <[email protected]> wrote: >> In addition to the above, I also redefined the index from "has >> local_start_time" to "indexes local_start_time". >> >> Then: >> >> Events::Event.search('*amanda*' >> ,:conditions=>{:local_start_time=>1.year.ago..Time.now}).total_entries >> => 0 >> >> Also, I had read somewhere that times had to be converted to ints when >> used in an index, so I tried: >> >> Events::Event.search('*amanda*' >> ,:conditions=>{:local_start_time=>1.year.ago.to_i..Time.now.to_i}).total_en >> tries >> => 0 >> >> On Oct 15, 9:31 am, Jeff Luckett <[email protected]> wrote: >> >> >> >>> Ok, sphinx upgraded to 0.9.9 >> >>> rake ts:rebuild run with "has local_start_time" defined in the index: >> >>> > Events::Event.search_count >>> => 60397 >> >>> ...so that looks a little better. >> >>> Now when I run the query without the :with clause, it returns a >>> reasonable result (which it wasn't doing before), so that's an improvement: >> >>> Events::Event.search('amanda' , :start=>true).total_entries >>> => 205 >> >>> However, when I try and use the :with clause and a time range over the >>> last year, I get zero results: >> >>> Events::Event.search('amanda' , :start=>true, >>> :with=>{:local_start_time=>1.year.ago..Time.now}).total_entries >>> => 0 >> >>> So, for a sanity check, I ran the following: >> >>> ree-1.8.7-2010.02 > ids = Events::Event.search('amanda' , :start=>true, >>> :per_page=>400 ).collect{|res| res.id} >>> ree-1.8.7-2010.02 > ids.count >>> => 205 >> >>> Now I've got an array of event_ids for the full search result ( I guess >>> I could have used search_for_ids, but I wanted to compare apples to >>> apples). So, now I'll do just a standard Rails model count query on the >>> above ids, and include the date range, which /should/ be the functional >>> equivalent of the Sphinx query using the :with clause. >> >>> ree-1.8.7-2010.02 > Events::Event.count(:all, :conditions=>["id in (?) >>> and local_start_time between ? and ?",ids ,1.year.ago, Time.now]) >>> => 142 >> >>> On 10/14/10 11:39 PM, Pat Allan wrote: >> >>>> Ah, bugger. Well, let's see if Sphinx 0.9.9 helps matters. > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
