date algorithm

skdzines

I need to know if anybody knows of an algorithm to take a date from a database and compare it to todays date.

What I'm doing is pulling a startdate from our database in 0000-00-00 format. Say the date is 2003-09-07, the person would have signed up on that date for periodic billing on the same day of everymonth thereafter. I'm trying to do an update billing script for if a customer needs to update cc info. I'm required by our merchant to pass a startdate, but it has to be in the future and not the past. So if they signed up originally on 2003-09-07 and did an update on 2003-11-15, I would need the date to be passed for updating to be 2003-12-07, keeping the same billing day of the month.

So I need to compare the startdate to the current date and determine how many days it will need to add to take it to the next month but same day of the month.

I also need it to get rid of the '-' in 2003-09-07 and send it like this 20030907.

I hope someone can help me.

Thanks.

Sxooter

This is all SQL-92 spec stuff. Unfortunately, the most popular of Open source databases does not yet support intervals.

In a database that does support intervales, such as postgresql, db2, oracle, informix, sybase, or mssql server, you can do it like this:

select cast ('20020304' as timestamp) - cast('30 days' as interval);

by the way, the SQL spec only technically supports timestamps of the format:

yyyymmdd hh:mm:ss +/-tz

So you should be able to store your dates anyway you want, and they should get converted to some internal format, and you should be able to specify the display format on output with some built-in for most databases.

Not sure if MySQL 4.x supports intervals yet. If not, it would be nice if they'd get around to adding them. They're quite useful.

Note that select cast ('20020304' as timestamp) - cast('30 days' as interval); isn't the same as select cast ('20020304' as timestamp) - cast('1 month' as interval);

since a month may not be exactly 30 days. Doing this by hand is a bitch. Letting a database do it and get it right is much easier.

skdzines

okay, so with the following code below, how would I incorporate the algorithm?

if($form=="form1"){
	if(empty($orderID)){
		$error.="You must enter your Order ID number.<br>\n";
	}
$ID=mysql_fetch_row(mysql_query("SELECT ID, startdate, cycle FROM customer_info WHERE ID='".addslashes(trim($orderID))."'"));
if ($ID[0]<=0){
		$error.="There is no matching Order ID in our Database.<br>\n";
	}else{
		$startdate=$ID[1]; 
		$cycle=$ID[2]; 
	}
	if(!empty($error)){
		$form="form1";
	}else{
		$form="form2";
	}
}

I'm still really new to all of this db stuff.

Thanks.

Sxooter

ACtually, my point was that in a SQL-92 database it would be fairly easy, but with MySQL you're gonna have to do it by hand.

What you'll have to do is just count up from today's date to the first date in the next month that has the same day of the week.

You'll need the date and / or the mktime functions to do this.

In a loop you can add 606024*7 seconds (seconds, minutes, hours, days in a week respectively) to the timestamp from right now to get next week, and use date to see if the month changed.

$count = time();
$interval = 60*60*24*7;
$this_month = date("n");
$foundit=0;
while (!$foundit){
  $count+=$interval;
  if (date("n",$count)!=$this_month) $foundit=1;
}
print date("Ymd",$count);

skdzines

Thank you very much Sxooter, It is sort of working for month to month billing. The first time I tested it, it gave me exactly one month from the startdate which was 2003-09-07. So it returned 2003-10-07. I then changed the startdate in the db to 2003-08-16 and it gave me back 2003-11-04 for the next billdate(startdate). It should have gave me 2003-10-16.

Now I need to ask you how to do it if they are on semi-annual or annaul billing. I need the algorithm to figure 26 weeks or 52 weeks from the startdate but also take into consideration of the current date to figure the time difference. If the orginal startdate was set on 2002-01-01 and it is now 2003-05-06 the next billdate stored in $startdate would need to be 2004-01-01.

I tried doing this but it isn't working:

if ($ID[0]<=0){
		$error.="There is no matching Order ID in our Database.<br>\n";
	}
elseif($ID[2]=='m1'){
	$count = time($ID[1]); 
	$interval = 60*60*24*7; 
	$this_month = date("n"); 
	$foundit=0; 
	while (!$foundit){ 
  		$count+=$interval; 
  		if (date("n",$count)!=$this_month) $foundit=1; 
	}
	}elseif($ID[2]=='y1'){
	$count = time($ID[1]); 
	$interval = 60*60*24*7*52; 
	$this_month = date("n"); 
	$foundit=0; 
	while (!$foundit){ 
  		$count+=$interval; 
  		if (date("n",$count)!=$this_month) $foundit=1; 
	}

	$startdate=date("Ymd",$count); 
	$cycle=$ID[2]; 
}

Any suggestions?

Thanks

Sxooter

Assuming you need 26 weeks plus today's date, you should be able to just add 606024726 to get the number of seconds from today. Do you have more advanced selection criteria than that? Like a certain month it had to be etc...?

skdzines

Sxooter, I actually edited my last post probably while you were reading it. So I have added a couple of things that you didn't see the first time.

Actually I wouldn't need 26 weeks from todays date, I would need 26 weeks from the original startdate, which is what I'm pulling from the database. If it hasn't been 6 months from the startdate yet, just add 26 weeks to startdate, otherwise if it has been 27 weeks(for example) from the startdate add an additional 26 weeks from startdate making it a year. If it has been 53 weeks from startdate, it would need to add 78 weeks from startdate.

It needs to always keep the same day of the month from startdate. If the value stored in cycle is m1, then it just calculates the next month from the day of the original startdate. And so on. So it could be monthly intervals, 6 month intervals or 12 month intervals from startdate always keeping the same day of the month from startdate. But it always has to be a date in the future, it can't be a date that has already passed.

Thanks