Search code examples
sphinxthinking-sphinx

Thinking sphinx string date attribute

I have a column 'value' of type TEXT. It can contain regular text, and also dates in string format. I index the column with indexes value. Can I also add an attribute that converts this column to an integer? I tried both of the following:

has "str_to_date(model.value, '%Y-%m-%d')", as: :value_as_date, type: :integer
has "CONVERT(model.value, DATETIME)", as: :value_as_date, type: :integer

Thanks

Solution

  • There's two approaches here... Sphinx has a timestamp (datetime) data type for attributes, so you could use that:

    has "CONVERT(model.value, DATETIME)", as: :value_as_date, type: :timestamp

    Or, it may be better to use an integer representing the date (because it sounds like you don't ned the time aspect) by extracting the date from the string, and then converting the %Y%m%d value to an integer (note, no hyphens in the date format).

    has "... more complex SQL snippet ...", as: :value_as_date, type: :integer

    In that situation, you'd then need to convert any dates for filters to integers of the same format as well.

    All of this aside: you'll want to have something in your SQL snippets that handles situations where your value column holds something other than a date. Or perhaps it's worth pulling those date values out into a separate column to have more consistent data? (Easy to suggest without knowing the full context of your app, I know.)