CODEWITHSUNDEEP
phpmysqlsphinx
Imran Omar Bukhsh
Imran Omar Bukhsh

Reputation: 8071

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 up 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

Upvotes: 0

Views: 3754

Answers (2)

Martin
Martin

Reputation: 6687

For newer versions of Sphinx you need to use SetFilterRange, i.e.:

$createdOn = strtotime('2011-8-4');
$cl->SetFilterRange("createdOn", $createdOn, $createdOn + 86400);

Upvotes: 1

Iaroslav Vorozhko
Iaroslav Vorozhko

Reputation: 1719

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("");

Upvotes: 4

Related Questions