Indexing on created_at timestamp issue

Joel Andritsch's Avatar

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

    Pat Allan's Avatar

    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

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac