8

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

5
  • 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
    Commented Aug 28, 2015 at 17:33
  • @Vérace: how would I apply that function..?
    – MDF
    Commented Aug 28, 2015 at 17:55
  • @Erik My bad for Grant2 end date... should be 2014-05-31!
    – MDF
    Commented Aug 28, 2015 at 17:57
  • Try SET NEW.montant_annuel = year(fin)-year(debut)+1 Commented Aug 28, 2015 at 19:53
  • Try: TIMESTAMPDIFF(DAYS,NEW.début,NEW.fin); for days and TIMESTAMPDIFF(YEAR,NEW.début,NEW.fin); for years.
    – oNare
    Commented Aug 28, 2015 at 20:16

2 Answers 2

12

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
2
  • 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
    Commented Sep 1, 2015 at 12:53
  • 1
    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
    Commented Sep 1, 2015 at 13:05
0

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.

EDIT:

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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