Home → Problems →

Indexing on created_at timestamp issue

Admin Functions

• Remove

Joel Andritsch

26 Dec, 2011 06:17 AM

So I'm attempting to get flying-sphinx running on my Rails 3 application on Heroku and I am running into an issue
when indexing the 'created_at' and 'updated_at' attributes for sorting purposes. Sphinx seems to stop partying
when this occurs:

ERROR: index 'post_delta': sql_range_query: ERROR: invalid input syntax for type timestamp: ""
The index definitions in my model look like:

define_index do

indexes title
indexes content
indexes slug
indexes month
indexes year
indexes created_at, :sortable => true
indexes updated_at, :sortable => true
set_property :delta => true

end

Removing the index on created_at and updated_at allows indexing to succeed. I'm not sure what is wrong. Anyone run into this and have a solution?

1. Support Staff 2 Posted by Pat Allan on 26 Dec, 2011 09:21 AM

   

   Hi Joel

   If the only reason you're referencing created_at and updated_at is for sorting (and I'd be surprised if you need them as text data in fields), then attributes is what you should be using instead.

   This means you'll want to use the has method instead of indexes:

   has created_at, updated_at
   

   Attributes are always sortable (that's one of their main purposes in Sphinx - indeed, when you specify :sortable for a field, it just creates a matching attribute under the hood).

   You can find more information here:
   http://freelancing-god.github.com/ts/en/sphinx_basics.html
   http://freelancing-god.github.com/ts/en/indexing.html#attributes

   Also, given this is a Sphinx/Thinking Sphinx issue, not Flying Sphinx, you can run it all locally and you'll hit the same issues - though I don't mind the questions being asked here anyway :)

   Any more questions, don't hesitate to ask.

   Pat

   Admin Functions

   • Remove