2

I am using the MySQL TIMESTAMPDIFF() function to calculate the difference in months between two dates, but there is a problem with overflow.

Say I have a date, and I use MySQL to calculate the same date 1 month later:

select DATE_ADD('2018-10-31', INTERVAL 1 MONTH) returns 2018-11-30

So, now I have two dates, 2018-10-31 and 2018-11-30.

Now I would like to calculate the interval in months between those dates, but

select TIMESTAMPDIFF(MONTH, '2018-10-31', '2018-11-30') returns 0, because of the number of days in November that DATE_ADD() adjusts for.

So, given two dates, which are calculated as above with DATE_ADD() and an interval of n months, is it possible to use either TIMESTAMPDIFF() or a different function to calculate n?

roelvanmeer
  • 356
  • 4
  • 18
  • For now I am using `TIMESTAMPDIFF(MONTH, $date1, DATE_ADD($date2, INTERVAL 3 DAY))` which looks ugly but seems to work well. – roelvanmeer Sep 04 '18 at 18:28
  • What happens if you use timestampadd() instead of date_add()? – Shadow Sep 04 '18 at 18:31
  • Same thing. Even then: the problem is in getting the _difference_ in months. The two dates are a given. I mention DATE_ADD() here only to show how they are related. – roelvanmeer Sep 04 '18 at 18:40

1 Answers1

1

One solution that you can use is to check the difference between days of the two dates. In this case, you would have 30 days, so you might consider it a month or less apart. In MySQL documentation there are some examples where one-month validation is described between periods of more than 31 days.

select TIMESTAMPDIFF(DAY,'2018-10-31','2018-11-30');
Result: 30
Lucas Eduardo Scrini
  • 39
  • 4
  • Yes, that would work when the dates are one month apart, but it doesn't work when comparing, say 2012-01-31 and 2019-02-28. – roelvanmeer Sep 04 '18 at 18:42
  • Actually, with the right division this could work quite well: `ROUND(TIMESTAMPDIFF(DAY, '2012-01-31', '2112-01-31') / 30.4375)` . The value 30.4375 is the average number of days per month when taking into account there's one leap day every four years. – roelvanmeer Sep 04 '18 at 20:54
  • 1
    I think about do this. But you never can take a period less than zero. This dates ('2018-10-31', '2018-11-15') never will return zero. – Lucas Eduardo Scrini Sep 04 '18 at 21:05