Yes. MySQL is inheriting from the system time zone which matches Rails' timezone.
Perhaps worth mentioning is that Time.zone.now.to_i and Time.now.utc.to_i return the same values which is expected since it is seconds since the epoch. However, the integer value stored in sphinx is literally an integer value in the future. $ date Tue Nov 3 12:26:56 PST 2009 $ script/console Loading development environment (Rails 2.3.4) >> Time.now => Tue Nov 03 12:27:38 -0800 2009 >> Time.zone => #<ActiveSupport::TimeZone:0x28d1920 @tzinfo=#<TZInfo::DataTimezone: America/Los_Angeles>, @utc_offset=-28800, @name="Pacific Time (US & Canada)"> mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) On Nov 2, 3:28 pm, Pat Allan <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
