Search code examples
datetimeindexingsphinx

indexing datetime in sphinx

I have some datetime fields in my database and I want to search on these fields. There is sql_attr_timestamp property in sphinx that is not indexed data and is just for filtering and also applies to SQL source types (mysql, pgsql, mssql) only. So, Is there a way for indexing datetime fields (or a component of a date) in sphinx?

Solution

  • Full-text part of a Sphinx query is not mandatory, i.e. you can skip it and make queries like:

    select * from idx where published = ...

    or

    select * from idx where inserted > ...

    If you use SphinxAPI you can just make your query empty which will do the trick. Another trick which might make sense in some cases is to put your time values into a full-text field rather than an attribute (use would just need to NOT specify sql_attr_timestamp for that). This may increase performance in some cases (e.g. when you have lots of documents, but only few of them match the criteria). In this case think about:

    • how the time value will be tokenized in an FT field, perhaps you will need to make YYYYMMDD instead of YYYY-MM-DD to make sure it remains a single word
    • whether you want it to be matched unintentionally when one uses a query which uses all your fields, if you don't you can wrap it in some special prefix/suffix, e.g. YYYY-MM-DD would become
      dtYYYYMMDDdt and then the chance someone would find it is much lower than when it's just YYYYMMDD or YYYY-MM-DD (if - is a separator)