Search code examples
postgresqlsphinx

Sphinx indexes datetime field incorrectly

I am running a PostgreSQL database. I have a table with a field of timestamp without time zone type. enter image description here

I index this table with Sphinx, here is the part of my config.txt: 

source ad: base
{
sql_query_range = SELECT MIN(id), MAX(id) FROM ad
sql_range_step = 1000
sql_query =\
    SELECT ad.id, \
           ad.title, \
           ad.category, \
           ad.subcategory, \
           ad.price, \
           ad.currency, \
           ad.url, \
           ad.photo_urls, \
           ad.city AS location, \
           ad.ad_id, \
           ad.date \
    FROM ad \
    WHERE ad.id BETWEEN $start AND $end

sql_field_string = title
sql_attr_uint = category
sql_attr_uint = subcategory
sql_attr_uint = location
sql_attr_uint = price
sql_attr_string = url
sql_attr_string = ad_id
sql_attr_string = photo_urls
sql_attr_multi = uint filtr from query; \
        SELECT ad_id, filter_value_id \
        from ad_filter_value_list
sql_attr_timestamp = date
}

But in the indexed table the field date contains only the value of the year, i.e. "2015", not the full date. Where can be the mistake?

Solution

  • Sphinxes timestamp attribute type is an (unsigned) integer. Really its no different to sql_attr_uint in function.

    Its intended for working with a unix-timestamp which is a plain integer.

    In mysql there is a UNIX_TIMESTAMP() function to convert the native datetype to a unix-timestamp. Hopefully PostgreSQL has something similer. Looks like extract() function? https://dba.stackexchange.com/questions/2796/how-do-i-get-the-current-unix-timestamp-from-postgresql