Ah, great detective work! This seems to be a difference between MySQL and Postgres - the former just returns 0 if it's before the epoch.
Your casting approach is definitely the best way forward - you should be able to do something like the following: has "cast the timestamp to a bigint", :as => :old_date, :type => :bigint That should hopefully keep things working without needing to hack into TS's internals (and maybe this is what you're doing already?) Cheers -- Pat On 25/02/2010, at 3:42 AM, Terry wrote: > Hey, Pat. Thanks for the reply. > > Here's what I figured out. By taking the pieces of the query that TS > generates for the Sphinx configuration file, I was able to isolate the > source of the error to an element of the SELECT clause of the query: > 'cast(extract(epoch from "patients"."birth_date") as integer) AS > "birth_date"'. The issue is that some of the people whose birth dates > I was trying to work with are very old, and therefore the seconds- > since-epoch representation of their date is a large negative number, > larger than -2^31. > > When I tweaked the cast_to_datetime method in lib/thinking_sphinx/ > adapters/postgresql_adapter.rb so that instead of casting to an > integer, it casts to a bigint, the error is fixed. Sphinx generates > the indexes, and life is beautiful again. (Note that I am using the > id64-enabled version of Sphinx.) > > Summary: > * TS casts date/time fields as 32-bit integers (holding seconds since > the epoch). > * For times far from the epoch, this can overflow the 32-bit limit. > * This can be fixed by casting these as 64-bit integers instead. > > Hope this helps others; thanks again for such a great tool. > Terry > > On Feb 24, 3:07 am, Pat Allan <[email protected]> wrote: >> Hi Terry >> >> I wonder if it's actually PostgreSQL complaining about the values, not >> Sphinx... can you try editing the SQL for that first column in the >> development.sphinx.conf file to cast the result as a BIGINT? Run rake >> ts:reindex instead of ts:index, as the latter will overwrite the manual >> changes to the conf file. >> >> Cheers >> >> -- >> Pat >> >> On 20/02/2010, at 8:46 AM, Terry wrote: >> >>> Pat, >>> I've been really happy using thinking_sphinx for about a year now, so >>> I appreciate your work on it. >>> I'm having exactly the same error as JB; right down to the fact that >>> we're both running against PostgreSQL databases. >>> I tried switching to Sphinx 0.9.9-id64-release (r2117), and it doesn't >>> seem to have helped. Do you have any other ideas about how I might >>> address this? >>> Thanks, >>> Terry >> >>> On Feb 12, 7:53 pm, Pat Allan <[email protected]> wrote: >>>> Hi JB >> >>>> The problem is that with the * 4, the results are bigger than 32 bits... >>>> so you'll need to compile Sphinx with the --enable-id64 flag, to allow 64 >>>> bit document ids. >> >>>> Give that a spin, see if it helps. >> >>>> Cheers >> >>>> -- >>>> Pat >> >>>> On 13/02/2010, at 4:35 AM, johnnybutler7 wrote: >> >>>>> Hi, >> >>>>> I have 4 models to index, 3 of them work fine but 1 keeps hanging when >>>>> building the indexes. Ive trawled the forums and read solutions for >>>>> similar issues but these don't seem to work for me. >> >>>>> The problem table which hangs the indexing is called Category and has >>>>> the appropriate id key field etc so should in essence work so i am a >>>>> bit stumped on that one. >> >>>>> I have read about the issue with the large id's and this does apply to >>>>> my tables. All 4 tables id's are pretty big for example 1900639709 is >>>>> an id field for one of the models called product. This table seems to >>>>> index ok but the category model who's id starts at 1600578709 just >>>>> hangs. >>>>> In the config file i see the id * 4 which is probably causing the >>>>> issue. >> >>>>> Basically what can i do to fix this. Below is the output >> >>>>> distributed index 'host_application' can not be directly indexed; >>>>> skipping. >>>>> indexing index 'manufacturer_core'... >>>>> ERROR: index 'manufacturer_core': sql_range_query: ERROR: integer out >>>>> of range >>>>> (DSN=pgsql://postgres:*...@localhost:2810/av3_live). >>>>> total 0 docs, 0 bytes >>>>> total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec >>>>> distributed index 'manufacturer' can not be directly indexed; >>>>> skipping. >>>>> indexing index 'product_core'... >>>>> ERROR: index 'product_core': sql_range_query: ERROR: integer out of >>>>> range >>>>> (DSN=pgsql://postgres:*...@localhost:2810/av3_live). >>>>> total 0 docs, 0 bytes >>>>> total 0.014 sec, 0.00 bytes/sec, 0.00 docs/sec >>>>> distributed index 'product' can not be directly indexed; skipping. >>>>> Loaded suite /opt/local/bin/rake >>>>> Started >> >>>>> thanks >> >>>>> JB >> >>>>> -- >>>>> You received this message because you are subscribed to the Google Groups >>>>> "Thinking Sphinx" group. >>>>> To post to this group, send email to [email protected]. >>>>> To unsubscribe from this group, send email to >>>>> [email protected]. >>>>> For more options, visit this group >>>>> athttp://groups.google.com/group/thinking-sphinx?hl=en. >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Thinking Sphinx" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
