Sphinx search based on datetime
I have a field called 'createdOn' which of the type 'timestamp'. The values stored in it are as such e.g.: 2011-08-07 00:00:00
I would like to search by this attribute. For example in mysql I would do this :
SELECT * FROM `posts` where deleted = 1 and date(createdOn) = '2011-8-4'
I tried to set it u开发者_如何学Gop in sphinx as such :
sql_query = \
SELECT id, deleted, upvotes, DATE(createdOn) as createdOn, thread_title, first_post \
FROM posts
sql_attr_bool = deleted
sql_attr_timestamp = createdOn
and in php:
$cl->SetFilter ( "deleted",array(1));
$cl->SetFilter ( "createdOn", '2011-8-4');
$result = $cl->Query("");
But this gives me an assertion failed error.
Thanking you
Attribute timestamp should have integer value.
In sphinx.conf:
sql_query = select UNIX_TIMESTAMP(createdOn) as createdOn from ...
sql_attr_timestamp = createdOn
and in php:
$cl->SetFilter ( "createdOn", strtotime('2011-8-4'));
$result = $cl->Query("");
For newer versions of Sphinx you need to use SetFilterRange
, i.e.:
$createdOn = strtotime('2011-8-4');
$cl->SetFilterRange("createdOn", $createdOn, $createdOn + 86400);
精彩评论