I stored "date of birth" in Sphinx search engine as timestamp (integer)
(eg)
User DOB is "12-01-1960" (Age is 55)
Sphinx side: 3980298496 (MySQL handle this from DB to Sphinx)
In PHP side, For search purpose, I want to calculate time-stamp as follows but it gives negative value because PHP gives negative value if date is less than Jan 1 1970
Carbon::now()->subYears('55')->timestamp = -288316800
How do I make a positive time-stamp? so that I can do filter search from PHP. Or please suggest any other workaround.
-288316800 to 3980298496
I think you can handle it by using the DateTime object
$dateTime = new DateTime;
$dateTime->setTimestamp(-288316800);
var_dump($dateTime->format('Y')); // prints string(4) "1960"
$dateTime = DateTime::createFromFormat('Y-m-d', '1930-03-03');
var_dump($dateTime->getTimestamp()); // prints int(-1256990210);
Basically you don't care how it is stored in database, you can use the DateTime object to convert from/to timestamp any integer (positive or negative)
Sphinx's timestamp attribute is an unsigned 32bit integer. (its not actully any different to a uint attribute)
... so you couldn't store such a value directly in a timestamp attribute.
Sphinxes timestamp is not good for dates prior to 1970 (0 timestamp)
Personally I use mysql TODAYS function to get a nice simple integer for a date, and store that in the sphinx attribute. Quite easy to work with (although havent emulated the conversion as a php function, so still do
<?php
$days = getOne("SELECT TODAYS('1960-01-12')");
when running queries.
(Could also add a large offset to the raw timestamp to make it an positive integer, but that also negates the convenience of using sphinxes built in date processing)