formatting date and adding one year

[webguy262]

I must admit that dates and timestamps really confuse me!

 

I have a query that gets a field named "date_purchased" and returns it like this "2010-09-05 09:58:12"

 

What I need to do is add one year to the date, and display it like this "September 5, 2011"

 

Basically, it's displaying the end date of a one year subscription.

 

The database captures the purchase date with the order, so I want to grab that, add a year, and display it to the customer.

 

Any help would be most appreciated!

[fortnox007]

Why don't you add an extra column to your data base named expires. I am pretty sure there is a SQL function to add 1 year. I have to look that up, but if i recall it uses the word NOW() and INTERVAL. Ill check it out : )

[fortnox007]

I haven't found a nice query yet but i am pretty sure it's on this page:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

This way you let the database calculate the stuff, otherwise you have to let php do the math.

and that probably looks like this:

 

$purchased = time();

$expires = $purchased + 31 556 926 // seconds in 1 year.

 

And than insert these values in your database. At least this is how I would do it (i think lol ) without sql

 

I bet you can use timestamp() too and than make an if statement, that checks if 1 year has passed.

[webguy262]

I made some progress using strtotime to convert the db result to a timestamp.

 

Here's where I'm stuck:

 

Any idea why this works...

 

$convert_date = date('U', strtotime($orderdate));

 

But this fails...

 

$convert_date = date('U', strtotime("+1 year", $orderdate));

 

...and returns

 

31538010 (the date for which is 12-31-1970)

 

What am I missing?

[PFMaBiSmAd]

You can do this directly in your query - DATE_ADD(date_purchased, INTERVAL 1 YEAR)

[Psycho]

Having an expiration date column in the table may be useful if there is ever a chance that the expiration dates could ever be something different than exactly 1 year from the registration date. For example, if a customer calls to complain about a problem perhaps a service rep would be allowed to extend their subscription or what if customers are allowed to make a two year subscription. So, I would definitely look into that as a possible solution.

 

But, if the expiration date will always be 1 year from the registration date, you can easily display the date 1 year in the future by using strtotime()

 

Just modify the first parameter of the date function to customize the output to your liking

$registrationDate = "2010-09-05 09:58:12";
$expirationDate = date('M j, Y', strtotime("$registrationDate +1 year"));
echo "Your subscription will expire on $expirationDate";
//Output: Your subscription will expire on Sep 5, 2011 

[webguy262]

Both solutions look great.  I stuck with manipulating the date after the query and it works great.

 

Final piece is getting the number of days remaining until the expiration date.

 

I now have...

 

$orderdate = $orders['date_purchased'];

$exp_date = date('F j, Y', strtotime("$orderdate +1 year"));

 

 

And I found this code to calculate days remaining to a date...

 

$cdate = mktime(0, 0, 0, 12, 31, 2009, 0);

$today = time();

echo $today;

$difference = $cdate - $today;

if ($difference < 0) { $difference = 0; }

echo "There are ". floor($difference/60/60/24)." days remaining";

 

How do I get my expiration date ($exp_date) expressed in a way consistent with the $cdate var so the subtract operation works?

 

Or is there another way to do the days remaining calculation from the way the $exp_date var is already formatted?

 

Thanks again to the board... I am actually sarting to  get some date() & time() knowledge!

[PFMaBiSmAd]

Again, you can do this directly in the query when you retrieve the data -

SELECT *, DATEDIFF(CURDATE(),date_purchased) as days_remaining the_rest_of_your_query_here...

 

This would be available as $orders['days_remaining']

 

Edit: Actually, I don't think that gives the correct answer (I was not thinking straight when I wrote that), but you can still do this directly in the query.

[PFMaBiSmAd]

nevermind, reply needs some work before it would work.

 

Edit: The following query does combine both suggestions to get the expire date and the number of days left (tested this time) -

SELECT *, @exp:= DATE_ADD(date_purchased, interval 1 year) as expire_date, DATEDIFF(@exp,CURDATE()) as days_remaining the_rest_of_your_query_here...

[webguy262]

OK, I got the $exp_date to come straight out of the query. Thanks!

 

But, yes, the days remaining has to be calculated from the date + one year.

 

Is that what you noticed?

 

I assume the DATE_ADD value is not available within the query, right?

 

Thanks, guys, for the help!

[PFMaBiSmAd]

I edited my post above with a working query.

[webguy262]

Awesome!

 

Thanks again!

[fortnox007]

Having an expiration date column in the table may be useful if there is ever a chance that the expiration dates could ever be something different than exactly 1 year from the registration date. For example, if a customer calls to complain about a problem perhaps a service rep would be allowed to extend their subscription or what if customers are allowed to make a two year subscription. So, I would definitely look into that as a possible solution.

That's exactly what i was thinking : ) damn customers 

[PFMaBiSmAd]

If this was a real application, you would set it up as a credit/debit account. You would enter each transaction that affects the current account balance (which happens to be an expire date) as a separate row.

 

This would give you a record of when the account was started, any payments, and any other adjustments to the balance, along with who caused those transactions.