Calculating Subscription Time Left?

[Lukeidiot]

Okay well its pretty straight forward.

 

I am trying to calculate the Date a subscription will end, and how many days are left, based on

a few variables (Starting date, and Subscription length)

 

Stuff you might wanna know:

(example)(i need a calculation for this, based on starting date)

Subscription Start: June 24, 2009, 2:05 am

Subscription End: July 24, 2009, 3:00 am

Subscription Left: 30 days

 

Subscription options:

3 Day Trial (259200 seconds)

30 Days (2592000 seconds)

90 Days (7776000 seconds)

6 Months (15778463 seconds)

 

If you have any help on this issue, please feel free to make a post, thanks!

[Adam]

How are you storing all this information, MySQL presumably? What data do you have available? What data type is it?

 

The logic behind it is simple, but the code will vary depending upon the questions above.

[Lukeidiot]

How are you storing all this information, MySQL presumably? What data do you have available? What data type is it?

 

The logic behind it is simple, but the code will vary depending upon the questions above.

 

I am storing it into a mysql database

 

sub_length is in seconds

 

sub_end was just for testing, I plan to calculate the ending date with the starting date, and subscription length, but not quite sure how yet.

[Daniel0]

Well, say you know that someone purchased a 3 day trial subscription at June 24th 2009, 13:00. The UNIX timestamp for that is 1245837600. So that lasts 15778463 seconds, so it's simply 1245837600+259200=1246096800, which is the equivalent of June 27th 2009, 13:00 (pass it to date).

 

So, with the same information above, for 1245837600 < currentDate < 1246096800, if e.g. currentDate = 1246019568 (June 26th 2009, 14:32:48), you have 1246096800-1246019568 (or: currentDate - endDate) = 77232 seconds until expiration. There are 86400 seconds on one day, so that means there is 77232/86400=0.89 days left. You could call that 0 days left (see: floor).

 

As for your database, you should have a separate table for subscription types and join the info with a particular subscription. Something like this:

CREATE TABLE `subscriptions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `subscription_type_id` int(10) unsigned NOT NULL,
  `purchased_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subscription_type_id` (`subscription_type_id`)
);

CREATE TABLE `subscription_types` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

[Lukeidiot]

Well, say you know that someone purchased a 3 day trial subscription at June 24th 2009, 13:00. The UNIX timestamp for that is 1245837600. So that lasts 15778463 seconds, so it's simply 1245837600+259200=1246096800, which is the equivalent of June 27th 2009, 13:00 (pass it to date).

 

So, with the same information above, for 1245837600 < currentDate < 1246096800, if e.g. currentDate = 1246019568 (June 26th 2009, 14:32:48), you have 1246096800-1246019568 (or: currentDate - endDate) = 77232 seconds until expiration. There are 86400 seconds on one day, so that means there is 77232/86400=0.89 days left. You could call that 0 days left (see: floor).

 

As for your database, you should have a separate table for subscription types and join the info with a particular subscription. Something like this:

CREATE TABLE `subscriptions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `subscription_type_id` int(10) unsigned NOT NULL,
  `purchased_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subscription_type_id` (`subscription_type_id`)
);

CREATE TABLE `subscription_types` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

 

This has deemed to be very helpful, but I still have one problem.

 

How would I use this to calculate the End Date based on Subscription Length, and Subscription Start?

 

EDIT: Fixed

 

Heres what I used:

$today = date("F j, Y, g:i a");
$startDate = $row['sub_start'];
$endDate = strtotime($row['sub_end']);
$sub_endit = $row[sub_length] + strtotime($startDate);
$sub_endit2 = date("F j, Y, g:i a", $sub_endit);

$end_sub_length = strtotime($row[sub_length]) + strtotime(date("F j, Y, g:i a"));

$sub_left = dateDiff($today, $sub_endit2);

 

PS: Thanks for the help!