Re: Timestamp plus 365 days

[Michael Stassen]

My first question would be, "Why is this column is TIMESTAMP?".  It 
seems to me that if its purpose is to store the subscription start date, 
it should be of type DATE.  Usually, you use a TIMESTAMP column to 
automatically keep track of the last updated time for a row.

Either way (DATE or TIMESTAMP), if you want to add a year to the 
start_date, you should do just that.  Something like

  UPDATE subscriptions SET start_date = start_date + INTERVAL 1 YEAR
  WHERE customer_id = ...
Don't use +365 days, as some years (leap years) have 366.

  mysql> SELECT CURDATE(),
      -> CURDATE() + INTERVAL 365 DAY `+365 days`,
      -> CURDATE() + INTERVAL 1 YEAR `+1 year`;
  +------------+------------+------------+
  | CURDATE()  | +365 days  | +1 year    |
  +------------+------------+------------+
  | 2004-02-13 | 2005-02-12 | 2005-02-13 |
  +------------+------------+------------+
  1 row in set (0.01 sec)
You also need to decide what to do if a customer renews his or her 
subscription after it expires.  Will you add 1 year to the previous 
start date, or will you start from the renewal date?

Michael

Matthew Stuart wrote:

Am I able to add 365 days to an already existing TIMESTAMP on a 
subscription service? If so I was going to use a form on a web page to 
update it and in the insert statement use Now() + INTERVAL 365 DAY but 
after some consideration, this would be wrong.

This would cause a problem if a current subscriber updated their 
subscription for another year before their present subscription ran out. 
In such a case, how do I take a date, eg: 1st March 2004 and add 365 
days to it so that no matter when I edit it, it returns the date 1st 
March 2005?

Do I have to change the column type in the database or is it some clever 
sql - or both?

Thanks

Mat

--------------------------

Matthew Stuart
11 Yew Tree Close
Middleton Cheney
Banbury
Oxon
OX17 2SU
01295 713813
07803 207734
--------------------------




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]