Dates before 1970 e.g. Date of Birth

[Kevin Monk]

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.

[Kevin Monk]

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.

[Pat Allan]

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