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! :)

  • Comme a dit l'autre (@Erik) - t'inquiète pour ton anglais (don't worry about your English). Have you looked at the YEAR function? – Vérace Aug 28 '15 at 17:33
  • @Vérace: how would I apply that function..? – MDF Aug 28 '15 at 17:55
  • @Erik My bad for Grant2 end date... should be 2014-05-31! – MDF Aug 28 '15 at 17:57
  • Try SET NEW.montant_annuel = year(fin)-year(debut)+1 – Jehad Keriaki Aug 28 '15 at 19:53
  • Try: TIMESTAMPDIFF(DAYS,NEW.début,NEW.fin); for days and TIMESTAMPDIFF(YEAR,NEW.début,NEW.fin); for years. – oNare Aug 28 '15 at 20:16

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
  • Great! The only problem with that is that it considers 2012-01-01 to 2012-12-31 as 0 year... it will only consider 2012-01-01 to 2013-01-01 as one year... – MDF Sep 1 '15 at 12:53
  • Solved it, just add +1 as @Jehad suggested it. SET NEW.montant_annuel_UL = NEW.montant / (TIMESTAMPDIFF(year,NEW.début,NEW.fin)+1) I just hope my grants dates will always be from the 1st of the month to the last day of another month... – MDF Sep 1 '15 at 13:05

I can't comment on these boards, but really credit should go to @Vérace.

Look at the TimeStampDiff, it looks like it should subtract/add your dates and can output the result in months. Note that without the timepart, it will use 00:00:00 as the timepart.


SET NEW.montant_annuel = NEW.montant * (TIMESTAMPDIFF(MONTH, NEW.début, DATE_ADD(NEW.fin,INTERVAL 86399 SECOND)))

I don't have a MySQL installation to test, but should be similar in function to SQL Server with different syntax.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.