search by date based on time()

how to search time() values with mktime

[phparion]

Hi

i am modifying a website where i want to make a search facility e.g i want to search the users who signed up between 2006-10-25 and 2006-10-27

but the previous developer has used time() function to insert joining date of the user sign up and also the field type is BIGINT(20) so now when i use the following code it doesnt give me the accurate results,

PHP Code:
$spitter = explode("-",$_POST['svalue']);
$year = $spitter[0];
$month = $spitter[1];
$day = $spitter[2];
$date = mktime(0,0,0,$month,$day,$year,-1);

the value in $date is different than the value generated by time() function today ..

I was wondering if someone can help me that how can i search database by date using date format year-month-day (0000-00-00) where the database stores sign up date as BIGINT and time() function is used to insert this field values.

e.g

PHP Code:
insert into tablename .... date) values(.... time() )

thanks

[RonPK]

$date = mktime(0,0,0,$month,$day,$year,-1);
the value in $date is different than the value generated by time() function today ..

That makes sense, as time() will return the current timestamp and mktime(0,0,0,m,d,y) returns the timestamp at 00:00:00 o'clock.

Maybe a simple db lookup with BETWEEN will do?

SELECT username WHERE signuptimestamp BETWEEN start AND end

[phparion]

but the problem is that $start and $end values doesnt match the ones stored in database,

e.g i just want to seach the users who signed up today i.e 2006-10-27 then the value i get from mktime is different from the values stored in the database and it never matches that values because in this my query shape is

select * from table where joindate=$mktimevalueoftodaysdate

and that value never matches with the one stored in db..

so for a period the query is working but for a single day it doesnt work...

[RonPK]

users who signed up between 2006-10-25 and 2006-10-27

$startdate = '2006-10-25'; 
$thrudate = '2006-10-27'; 
$sql = "SELECT *  
FROM table  
WHERE joindate BETWEEN UNIX_TIMESTAMP('$startdate') AND UNIX_TIMESTAMP('$thrudate 23:59:59')";

I ran a test to check whether UNIX_TIMESTAMP( '2006-10-1' ) returns the same as UNIX_TIMESTAMP( '2006-10-01' ). Using MySQL the results were identical.

[edited by: RonPK at 12:39 pm (utc) on Oct. 27, 2006]

[phparion]

your suggested query is working after some improvisation to fit my system.

thanks a lot