MySQL – How to make a DATEDIFF in years

dateMySQLtrigger

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up.

I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed my database using PHPmyadmin and MySQL Workbench.

In my table subventions (grants), I created a trigger to be able to calculate the annual amount of money of a grant, using my columns subventions.début (beginning), subventions.fin (end) and subventions.montant (amount).

As DATEDIFF only gives me an answer in days, I naively thought that I only had to divide it by 365 days to obtain my answer. It works if my beginning date is the first of january and the end date the 31st of December, but does not if it stars somewhere else during the year. Here is my trigger:

SET NEW.montant_annuel = NEW.montant / (datediff(NEW.fin,NEW.début)/365)

Here is my table and the result in the 'montant_annuel' column:
Tableau : Subventions

If I use 365.25 (which is the "real" number of days in a year), it does not work either.

Thanks! 🙂

Best Answer

Yes, TIMESTAMPDIFF is the best approach:

select TIMESTAMPDIFF(year,'2011-01-01', now() ) ;  --> 4
select TIMESTAMPDIFF(year,'2011-08-28', now() ) ;  --> 4 
select TIMESTAMPDIFF(year,'2011-09-01', now() ) ;  --> 3
Related Question