Is the

time_purchase

column of type TIMESTAMP?

Yes it is
Thanks

And

$dif

is also a TIMESTAMP? Don't you want a duration instead?

dif is also a time stamp value
I am doing a cron that checks for renewing membership
do you mean something like [webmasterworld.com]

I am not familiar with "duration"
any reading?
thanks

Duration is something like ...

1 DAY 
3 YEAR 
10 MINUTE

The MySQL units can be found in the DATE_ADD [dev.mysql.com] function.

You have to be careful when it comes to adding/subtracting datetime values. In some databases the only arithmetic operations that can be performed on DATETIME values are addition and subtraction and the operands can vary for each. For example, in some cases if a DATETIME value is the operand of addition the other operand must be a duration.

Try adding two dates together at your command line and see what is returned. For example, here is a query that I can run on a Windows server running MySQL 5.0.51a:

SELECT CURRENT_DATE - '2008-11-21'; 
// 20079193

Perhaps not quite what you expected.

What is in

$dif

?

Thanks coopster, you're in for a long reading

Duration: I thought it was a () I did not know about :)

I am trying to figure how adding 11 months and 20 days to a registration timestamp in order to send the first renew call.

$time_20 = strtotime(date("Y-m-d G:i:s", $now) . "-20days"."-11 months"); //echo "time_20 $time_20<P>";
$time_21 = strtotime(date("Y-m-d G:i:s", $now) . "-21 days"."-11 months"); //echo "time_21 $time_21<P>";

$db = new MySQL_Db2;
$db->connect($host, $un, $pw);
$db->query($db_db2);
$result = $db->query("
SELECT
time_purchase, customer_id
FROM
CCd_profile
WHERE
(time_purchase BETWEEN '$time_21' AND '$time_20')
");

$num=$db->num_rows($result);
if($num==0)
{
exit();
}


while ($row=$db->fetch_object($result) )
{
$time_purchase=$row->time_purchase; //echo"time purch $time_purchase<P>";
$customer_id=$row->customer_id;

$reg_time1=$time_purchase;
$reg_time1= date("Y-m-d G:i:s",$reg_time1);
$reg_time_20 = strtotime(date("Y-m-d G:i:s", strtotime($reg_time1)) . "+11 months"."+20 days"); //echo "2 nd regtime_20 $reg_time_20<P>";
$reg_time_21 = strtotime(date("Y-m-d G:i:s", strtotime($reg_time1)) . "+11 months"."+21 days"); //echo "2 nd regtime_21 $reg_time_21<P>";
//$second_20=date("m-d-Y", $reg_time_20);//echo "second time-20...: $second_20<P>";
//$second_21=date("m-d-Y", $reg_time_21);//echo "second time-21...: $second_21<P>";

///////////// get a value for reg time +11 months+20 days minus original reg time //////////////////////////////////
///////////// reason: we may only compare original from DB if adding to it 11 months and 20 days ///////////////////

$dif=($reg_time_20-$time_purchase);

$new=$time_purchase+$dif;

/////////////////////////////////////////////////////////////////////////////////

$db->connect($host, $un, $pw);
$db->query($db_db2);

$result2 = $db->query("
SELECT DISTINCT
ccc.username, ccc.customer_id, ccc.firstName, ccc.lastName, ccc.email, ccc.htmlEmail,
ccdp.customer_id, ccdp.productId, ccdp.time_purchase, ccdp.quantity
FROM
#*$!#*$! ccc, YYYYYY ccdp
WHERE
ccc.customer_id='$customer_id'
AND
ccdp.time_purchase+'$dif'='$reg_time_20'
AND
time_purchase+'$dif' <= '$reg_time_21' AND time_purchase+'$dif' >= '$reg_time_20'
");

Looks like you are going to update the registration timestamp each year upon renewal then? OK.

Well, in all reality, it looks like you are attempting to give 10 days advanced notice of renewal option. Rather than add 11 months and 20 days to the original registration date, have you considered using the 10 days instead and comparing it to the CURRENT_DATE? Here is an example of how to add one year and then back up ten days:

SELECT CURRENT_DATE + INTERVAL 1 YEAR - INTERVAL 10 DAY AS noticeDate; 
+------------+ 
¦ noticeDate ¦ 
+------------+ 
¦ 2009-11-21 ¦ 
+------------+

... and for that matter, if you didn't want to keep updating the registration date, you could just compare the month/day of the original registration date to the month/day ten days prior to CURRENT_DATE. Keep the year out of the equation entirely.

... and for that matter, if you didn't want to keep updating the registration date, you could just compare the month/day of the original registration date to the month/day ten days prior to CURRENT_DATE. Keep the year out of the equation entirely.

Ah, much easier, thanks for the help

... I should have left an example in case you were considering that route. Ten days prior to today's date would be November 21, 2008. So, let's pretend the original registration date was November 22, 2005. We should get a false (zero) return value on the following comparison:

SELECT RIGHT(CURRENT_DATE - INTERVAL 10 DAY, 5) = RIGHT('2005-11-22', 5) AS n; 
+---+ 
¦ n ¦ 
+---+ 
¦ 0 ¦ 
+---+ 
// Looks good, now change that registration date to the 21st: 
SELECT RIGHT(CURRENT_DATE - INTERVAL 10 DAY, 5) = RIGHT('2005-11-21', 5) AS n; 
+---+ 
¦ n ¦ 
+---+ 
¦ 1 ¦ 
+---+

Looks like a winner. Time to send off a registration renewal notice.