MYSQL How to perform custom month difference between two dates in MYSQL?

Question

My requirement is to compute the total months and then broken months separately between 2 dates (ie first date from table and second date is current date). If broken months total count is > 15 then account it as one month experience and if its les than 15 don't account that as 1 month experience.

Assume I have a date on table as 25/11/2018 and current date is 06/01/2019; the full month in between is December, so 1 month experience; and broken months are November and January, so now I have to count the dates which is 6 days in Nov and 6 days in Jan, so 12 days and is <= (lte) 15 so total experience will be rounded to 1 month experience

I referred multiple questions related to calculating date difference in MYSQL from stackoverflow, but couldn't find any possible options. The inbuilt functions in MYSQL TIMESTAMPDIFF, TIMEDIFF, PERIOD_DIFF, DATE_DIFF are not giving my required result as their alogrithms are different from my calculation requirement.

Any clue on how to perform this calculation in MYSQL and arrive its result as part of the SQL statement will be helpful to me. Once this value is arrived, in the same SQL, that value will be validated to be within a given value range.

Including sample table structure & value:

table_name = "user"

id | name | join_date 
---------------------
1| Sam | 25-11-2017
2| Moe | 03-04-2017
3| Tim | 04-07-2018
4| Sal | 30-01-2017
5| Joe | 13-08-2018

I wanted to find out the users from above table whose experience is calculated in months based on the aforementioned logic. If those months are between either of following ranges, then those users are fetched for further processing.

table_name: "allowed_exp_range"
starting_exp_months | end_exp_months
-------------------------------------
0 | 6
9 | 24

For ex: Sam's experience till date (10-12-2018) based on my calculation is 12+1 month = 13 months. Since 13 is between 9 & 24, Sam's record is one of the expected output.

What about `5/11/2018` and `20/12/2018` is that 0 months and 2 broken months totalling 47 days? — Nick, Dec 10 '18 at 05:13
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 10 '18 at 05:26
yes @Nick, if total is 47 days , since it is greater than my limit of 15 days, the difference is considered to be 1 month. — Malathy, Dec 10 '18 at 05:54
If anyone is interested to downvote any question, please provide a comment with a reason for downvote. Else, the OP would never know what is wrong leaving a high possibility to repeat the same. So, please show equal interest to explain the reason as you show to downvote. — Malathy, Dec 10 '18 at 08:54
@Malathy I've updated my answer based on the changes you made to your question. — Nick, Dec 11 '18 at 08:53

Nick · Accepted Answer · 2018-12-11T08:52:54.973

I think this query will do what you want. It uses

  (YEAR(CURDATE())*12+MONTH(CURDATE()))
- (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) -
- 1

to get the number of whole months of experience for the user,

  DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y')))
- DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))
+ 1

to get the number of days in the first month, and

DAY(CURDATE())

to get the number of days in the current month. The two day counts are summed and if the total is > 15, 1 is added to the number of whole months e.g.

SELECT id
     , name
     , (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) - 1 -- whole months
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END -- broken month
       AS months
FROM user

We can use this expression as a JOIN condition between user and allowed_exp_range to find all users who have experience within a given range:

SELECT u.id
     , u.name
     , a.starting_exp_months
     , a.end_exp_months
FROM user u
JOIN allowed_exp_range a
ON (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(u.join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - 1
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END
       BETWEEN a.starting_exp_months AND a.end_exp_months

Output (for your sample data, includes all users as they all fit into one of the experience ranges):

id  name    starting_exp_months     end_exp_months
1   Sam     9                       24
2   Moe     9                       24
3   Tim     0                       6
4   Sal     9                       24
5   Joe     0                       6

I've created a small demo on dbfiddle which demonstrates the steps in arriving at the result.

Thanks @Nick for your time and answer! Your answer helped me to derive my expected output. — Malathy, Dec 13 '18 at 13:53
Hi @Nick can you advise your answer for this thread. https://stackoverflow.com/questions/53768236/eloquent-how-to-use-alias-name-in-further-multiple-conditions ? — Malathy, Dec 14 '18 at 05:11
@Malathy I'm sorry - I don't know eloquent/laravel so I can't help you with that question. I'm sure someone else will be able to. — Nick, Dec 14 '18 at 05:57

MYSQL How to perform custom month difference between two dates in MYSQL?

1 Answers1

Linked