Indexing and searching models with 'date' type fields before 1970

[Murilo Soares Pereira]

Do we have any advances on this subject? I did a little googling and
didn't found any answers to that issue.

--
Murilo Soares Pereira
http://www.comp.ufscar.br/~murilo

[Murilo Soares Pereira]

The only solution that I can think of is storing date values as
strings, and them indexing them as fields, rather than attributes.
Then they wouldn't be converted to UNIX timestamps.

I think I'll do that now.

On Dec 27, 5:22 pm, Murilo Soares Pereira <murilo.soar...@gmail.com>
wrote:

[Pat Allan]

I've not got a solution into TS for this yet... but your email just got me thinking, would it work to have two integer attributes - one for the date, one for the time - eg: 20091228 and 115340233. Then you could sort by date and then time. And I think in the vast majority of cases, people won't want to filter on times, just dates...

Although, are you wanting the text representations of dates to be searchable by users? Or do you want to filter on them? For the latter, attributes is what's needed, but otherwise, definitely go with fields, as you've already found.

--
Pat

> --
>
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
>
>

[Murilo Soares Pereira]

Hey Pat, replying quickly as usual.

What I want is that the model that has the 'date' type field can be
ordered, example: Model.search("foo", :order => "date_field ASC").

--
Murilo Soares Pereira
http://www.comp.ufscar.br/~murilo

[Pat Allan]

Hi Murilo

Then splitting the datetime over two attributes, as I described below, is the best approach... I think you'll need to create SQL snippets for your attributes, maybe something like the following (assuming MySQL):

has "CAST(DATE_FORMAT(datetime_col, '%Y%m%d') as UNSIGNED)", :type => :integer, :as => :datetime_date
has "CAST(DATE_FORMAT(datetime_col, '%H%i%s') as UNSIGNED)", :type => :integer, :as => :datetime_time

And then to get the ordering working nicely, the following should do the job:

Model.search('foo', :order => 'datetime_date ASC, datetime_time ASC')

This is all theory though - I've not tried it myself, but I think it'll work :)

--
Pat

[Murilo Soares Pereira]

Hey Pat, thanks for the insights.

I'm using PostgreSQL and tried the snippets you provided, but thinking
sphinx can't generate the config file. I'm googling to see if this SQL
statement is valid in PostgreSQL.

And I'm not using the 'time' attribute, only 'date'.

Cheers.

--
Murilo Soares Pereira
http://www.comp.ufscar.br/~murilo

[Murilo Soares Pereira]

I did it. I configured it to work with PostgreSQL.

has "CAST(TO_CHAR(date_field, 'YYYYMMDD') as INTEGER)", :type
=> :integer, :as => :date_field
group_by "date_field"

These two configurations are needed inside the 'define_index' block.

Hope it helps someone :)

Thank you Pat!