Hi Kevin
I had realised this was a problem in Sphinx before, but hadn't found
the time to look at working around it... it's definitely complicated
by both databases and Sphinx expecting 1970 as the epoch, and Sphinx
not liking signed integers.
Your approach is useful to some extent, although pushing the epoch
back would confuse some of Sphinx's timestamp-related features, so I
don't think it's an implementation that I'll put into TS. Although I
don't have a *good* solution myself...
Good to know you've at least found something that works for you though.
Cheers
--
Pat
On 17/04/2009, at 12:32 AM, Kevin Monk wrote:
>
> Too anybody else who's using Thinking Sphinx for dates before 1970
> then this was my approach....
>
> I changed the SQL to:
> (TO_DAYS(`models`.`dob`) - TO_DAYS("1800-01-01")) AS `dob`
>
> The number of days since 1800.
>
> and then put an MVA condition in my search as follows:
>
> :with => {:dob => ((Date.today-params[:search]
> [:search_age_stop].to_i.years) - "1800-01-01".to_date).numerator...
> ((Date.today-params[:search][:search_age_start].to_i.years) -
> "1800-01-01".to_date).numerator},
>
> Not sure it's the best way to do it but it seems to work.
>
> On Apr 15, 5:25 pm, Kevin Monk <[email protected]> wrote:
>> Has this issue been resolved?
>>
>> I'd like a timestamp MVA so that I can filter by a person's age.
>>
>> UNIX_TIMESTAMP ???
>> ughh.. this thing is horrible. Only dates after 1970? Did nothing
>> happen before 1970?
>>
>> There's a man here back in 1999 making a fairly good argument for
>> UNIX_TIMESTAMP returning a signed rather than unsigned INT and
>> getting
>> flack from a lot of people who only consider dates to be file time
>> stamps.
>>
>> http://lists.mysql.org/mysql/15585
>>
>> So hey hum... it's not going to achieve what I wanted.
>>
>> I'm getting my hands dirty in the SQL again with nasty little things
>> like...
>>
>> (((TO_DAYS(`models`.`dob`) * 86400) +
>> TIME_TO_SEC(`models`.`dob`)) -
>> (TO_DAYS("1970-01-01") * 86400)) AS `dob`
>>
>> nasty stuff but it's better than treating every date before 1970 as
>> zero.
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
