Are your database and Rails app using the same time zone?
--
Pat
On 03/11/2009, at 4:58 AM, ThadeMark wrote:
>
> Thanks for the response Pat.
>
> It appears that Sphinx is saving the created_at timestamp in the
> future due to TimeZone and UTC conversions.
>
>>> Article.search('ts').last.sphinx_attributes
> Querying Sphinx: ts
> Article Load (0.6ms) SELECT * FROM `articles` WHERE
> (`articles`.`id` IN (16,17,18))
> => {"created_at"=>1257209892, "sphinx_deleted"=>0, "subclass_crcs"=>
> [3448190970], "title_sort"=>0, "class_crc"=>3448190970,
> "sphinx_internal_id"=>18}
>>> Time.now.to_i
> => 1257184331
>
> Notice the created_at is ~7 hours in the future.
>
>>> Article.search :with => { :created_at => 2.days.ago..Time.now }
> Querying Sphinx:
> => []
>>> Article.search :with => { :created_at => 2.days.ago..Time.now
>>> +1.day }
> Querying Sphinx:
> Article Load (0.5ms) SELECT * FROM `articles` WHERE
> (`articles`.`id` IN (18))
> => [#<Article id: 18, user_id: 34, title: "TS Test 2", synopsis: nil,
> body: "Testing Thinking Sphinx 2", state: "queued", rating: nil,
> view_count: nil, created_at: "2009-11-02 16:58:12", updated_at:
> "2009-11-02 16:58:12", delta: true>]
>
> On Oct 30, 9:02 pm, Pat Allan <[email protected]> wrote:
>> Carrying on from your last example, what's the output of the
>> following:
>> Article.search('ts').first.sphinx_attributes
>>
>> This should display the created_at value as Sphinx stores it, which
>> will hopefully give us some clues.
>>
>> --
>> Pat
>>
>> On 29/10/2009, at 3:27 PM, ThadeMark wrote:
>>
>>
>>
>>> I'm not able to get results for recent records when searching using
>>> date ranges. I've been debugging for hours now. Any help would be
>>> appreciated.
>>
>>> Using latest ThinkingSphinx (as of 2009/10/28), Sphinx (0.9.8.1),
>>> Rails (2.3.4), Ruby (1.8.7), and MySQL (Ver 14.12 Distrib 5.0.45) on
>>> Mac OS 10.5.8.
>>
>>> ### Article Model
>>> define_index do
>>> indexes title, :sortable => :true
>>> indexes body
>>
>>> has created_at
>>
>>> set_property :delta => true
>>> end
>>
>>> ### Schema
>>> create_table "articles", :force => true do |t|
>>> t.integer "user_id"
>>> t.string "title"
>>> t.string "synopsis"
>>> t.text "body"
>>> t.string "state"
>>> t.integer "rating"
>>> t.integer "view_count"
>>> t.datetime "created_at"
>>> t.datetime "updated_at"
>>> t.boolean "delta", :default => true, :null => false
>>> end
>>
>>> ### Console Session
>>> a = Article.create(:user_id => 34, :title => "TS Test", :body =>
>>> "Testing Thinking Sphinx")
>>> => Leaving out part of results code for privacy reasons
>>> Sphinx 0.9.8.1-release (r1533)
>>> Copyright (c) 2001-2008, Andrew Aksyonoff
>>
>>> using config file '/Users/thade/dev/rails/config/
>>> development.sphinx.conf'...
>>> indexing index 'article_delta'...
>>> collected 2 docs, 0.0 MB
>>> collected 0 attr values
>>> sorted 0.0 Mvalues, 100.0% done
>>> sorted 0.0 Mhits, 100.0% done
>>> total 2 docs, 60 bytes
>>> total 0.168 sec, 358.18 bytes/sec, 11.94 docs/sec
>>> rotating indices: succesfully sent SIGHUP to searchd (pid=11628).
>>> Querying Sphinx:
>>> Querying Sphinx:
>>
>>>>> a.valid?
>>> => true
>>
>>>>> Article.search :with => { :created_at => 1.day.ago..Time.now }
>>> Querying Sphinx:
>>> => []
>>
>>>>> 1.day.ago
>>> => Tue, 27 Oct 2009 21:12:29 PDT -07:00
>>>>> Time.now
>>> => Wed Oct 28 21:12:26 -0700 2009
>>>>> a.created_at
>>> => Wed, 28 Oct 2009 21:07:51 PDT -07:00
>>
>>> ### Let's 'rake ts:rebuild'
>>
>>>>> Article.search :with => { :created_at => 1.day.ago..Time.now }
>>> Querying Sphinx:
>>> => []
>>
>>> ### Let's try making the range go back further
>>
>>>>> Article.search :with => { :created_at => 30.days.ago..Time.now }
>>> Querying Sphinx:
>>> Article Load (0.7ms) SELECT * FROM `articles` WHERE
>>> (`articles`.`id` IN (14))
>>> => [#<Article id: 14, user_id: 93, title: "Test", synopsis: "Test",
>>> body: "Test", state: "active", rating: nil, view_count: 1,
>>> created_at:
>>> "2009-10-06 18:33:15", updated_at: "2009-10-06 18:35:00", delta:
>>> false>]
>>
>>> ### Success! We have an article result, although not the most recent
>>> one that was just created
>>
>>> ### Let's try searching with terms
>>
>>>>> Article.search 'ts'
>>> Querying Sphinx: ts
>>> Article Load (0.6ms) SELECT * FROM `articles` WHERE
>>> (`articles`.`id` IN (16))
>>> => [#<Article id: 16, user_id: 34, title: "TS Test", synopsis: nil,
>>> body: "Testing Thinking Sphinx", state: "queued", rating: nil,
>>> view_count: nil, created_at: "2009-10-29 04:07:28", updated_at:
>>> "2009-10-29 04:07:28", delta: false>]
>>
>>> ### Success! Sphinx has the latest article in there, just not
>>> finding
>>> it with the date range search
>>
>>> Any ideas?
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
