How can I calculate the difference between 2 dates?

[ConfusedOfLife]

Hi, I searched here and also the MySql mans, but couldn't find my function!

I don't wana simply subtract the year part of 2 different dates, but I need to subtract the whole part! Something like:

"select '2000-05-07' - '1994-03-05'"


And it should return "6-2-2", is this possible?!

[raf]

Hmm. This is, i think, quite impossible.
Well, if you need it to be absolutely correct, that is.

What would work is
select TO_DAYS('2000-05-07') - TO_DAYS('1994-03-05')

This will return the number of days in between. You ci-ould then devide this number by 365 to get the number of years (buth here would already be a slight error), and the remaining by 30 or 31 (another slight erro)

Or you could try to use the FROM_DAYS() like

select FROM_DAYS(TO_DAYS('2000-05-07') - TO_DAYS('1994-03-05'))

but i nevr tryed that, and it would also not be correct, since it would start from 0000-00-00 on, and the number if days in each year then, aren't necessarely the same.

So, i'd work with the number of days between the two date, which will be absolutely correct at all times.


If you really really need it, you can
-compute the number of days
-run a php script or use the FROM_DAYS() like above and store the result in $datediff and extract year, month and date
-have a second sql query like
SELECT DATE_ADD("1994-03-05", INTERVAL $year $month $day YEAR_MONTH_DAY)
- compute the difference between the generated date and '2000-05-07'
-depending on the difference, adjust the $datediff

I believe that would in almost all cases be absolutely correct

[ConfusedOfLife]

Thanks, I really don't need it that much! I was just curious since I was about to write a kind of sticky news control panel. It's not a simple news managing system that you enter your news for a special date for example and by searching for that day you find your news over there. It's a kind of warning system, it means when you enter your warning message (I'm writing this for a weather forecast company forecasting some storms or stuff like that!), you say how many days it should stay in the database and ppl can see it. For example you insert your warning today, and say it should stay for 5 days. So, I had 2 choices, having the reg_date and exp_date in the db, or having the reg_date and number_of_days_to_stay and then find the exp_date by a mysql query. I chose the latter, because I also needed to give them the chance of editing their post and change the exp_date. But then I found out that I don't know how to code the first way, because I couldn't find the date difference. You got the whole story?!

Anyways, thanks again

[raf]

Heuh ...

Intuitively, i'd think that registering the reg_date and exp_date is the way to go. Since the exp_date is an absolute date, and not something relative to the regdate or editdate.
So if exp_date is default 5 day's, i'd use

insert into table (regdate, expdate) values (Now(),(DATE_ADD(Now(), INTERVAL 5 DAY))

If the user specifies the number of days, then you replace the 5 by $_POST['numdays'] or so.
If the user selects the date, you replce the whole date_add function by that date.

If he likes to edit the expiredate, you just update the record and replace the expdate with th new value (best use some sort of callendar or dropdown to let them select the date)

Just some ideas. Don't know your app so the number-of-days-from-regdate might be better, but i can't immedeately think of a reason for it.

[ConfusedOfLife]

Thanks for your suggestions, but you know, it's a weather forecast thing, and I really don't know how many days they wana have their alerts on the page. So, putting that select box doesn't fit in here I think. Also, by saying edit, I mean when you entereda number of days for the alert to last, then you can edit that number too. Because our date system is Galali, that's very different with Gregorian date, I really can not afford to check the validity of an entered date and see if it's correct. You know, I don't know that much about dates, years, leap years and stuff! Also this way is more human like, I mean they deal with a number, and most of the times they know how many days they want it to stay there, so, I can always make that exp_date myself! But what you say is a cleaner way, I admit!