Jump to content


I want to add x days to a datetime

  • Please log in to reply
2 replies to this topic

#1 shaggycap

  • Members
  • PipPip
  • Member
  • 21 posts

Posted 10 February 2006 - 03:51 PM

I have a field in my DB called 'trial_start' which is a datetime with a default value of now().

There is another field in the DB called 'days' which is the no of days that the trial will run for.

I want another field, called 'trial_end' which is, you've guessed it, the 'trial_start' plus the number of days.

I am struggling as to what syntax to use, to calculate the 'trial_end' value. I assume I need to turn the days into seconds and add them onto the timestamp of the trial_start.

Can anyone help please as I'm struggling. Thanks

#2 fenway

  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 February 2006 - 07:50 PM

My first question is why store all three? You only need two of them, and the 3rd can always be calculated. Second, how much sense does it make to add a fixed number of days to a DATETIME column -- doesn't that mean the time of the trial_end value is meaningless?

Anyway, regardless, you can always use INTERVAL to set it (UNTESTED):

UPDATE yourTable SET trial_end = trial_start + INTERVAL days DAY WHERE....

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 obsidian

  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 February 2006 - 07:58 PM

... or, just do them all at once:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] tableName SET trial_end = DATE_ADD(trial_start, INTERVAL days DAY);
You can't win, you can't lose, you can't break even... you can't even get out of the game.

while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users