Perhaps you need a different "datestyle" setting.

[Lephyrius]

ERROR: index 'game_core': sql_range_query: ERROR: date/time field
value out of range: "0"
LINE 1: ...rray_agg(COALESCE("release_dates"."release_date", '0')), '
'...
^
HINT: Perhaps you need a different "datestyle" setting.

ERROR: index 'game_delta': sql_range_query: ERROR: date/time field
value out of range: "0"
LINE 1: ...rray_agg(COALESCE("release_dates"."release_date", '0')), '
'...
^
HINT: Perhaps you need a different "datestyle" setting.

I get these to errors I use thinking sphinx and PostgreSQL.
Is this a PostgreSQL or a Sphinx error or both?
What can I do about it? I want to sort the games with release_date.
The release_date migration looks like this:
change_table :release_dates do |t|
t.date :release_date
end
And the index looks like this:

define_index do
indexes release_dates.release_date, as: :release_date, sortable:
true
end

Hope I have provided enough info, :)

[Pat Allan]

This is a PostgreSQL error, but it's because Thinking Sphinx expects fields to be strings - the power of Sphinx's text comparison is wasted on dates and numbers.

It's worth asking: why are you indexing the release date? Do you expect users to search for parts of that date? Or is it just so you can sort by it? If so, it's much better to have it stored as an attribute instead of a field:

has release_dates.release_date, :as => :release_date

Now, though, the issue is that you're dealing with a collection of dates, and so which date for any given game do you want to sort by - the latest one? The oldest one? Some kind of average?

Happy to discuss further, but it'll certainly help if you can tell us what you're trying to do with those release dates in regards to searching :)

Cheers

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

[Lephyrius]

Nope, sorry I just want to sort it with the oldest release date and
handling nil = TBA value last.

[Pat Allan]

Well, you'll probably want to do something like this:

has "MIN(release_dates.release_date)", :as => :release_date, :type => :datetime
join release_dates # force the join if not used elsewhere

I'm not sure how MIN treats NULLs though, so you'll probably want to handle that.

--
Pat