I am a bit confused on the results i am getting when using the TIMESTAMPDIFF function
select '2021-05-01',CURRENT_DATE(),TIMESTAMPDIFF(MONTH,'2021-05-01',CURRENT_DATE()) as monthDiff;
select '2020-09-03',CURRENT_DATE(),TIMESTAMPDIFF(MONTH,'2020-09-03',CURRENT_DATE()) as monthDiff;
select '2021-08-18',CURRENT_DATE(),TIMESTAMPDIFF(MONTH,'2021-08-18',CURRENT_DATE()) as monthDiff;
This is the output for the above sql,
2021-05-01 CURRENT_DATE() monthDiff
2021-05-01 2021-12-08 7
2020-09-03 CURRENT_DATE() monthDiff
2020-09-03 2021-12-08 15
2021-08-18 CURRENT_DATE() monthDiff
2021-08-18 2021-12-08 3
Shouldn't the result for the difference between 2021-08-18 and 2021-12-08 be 4 ? Am i missing something ?
CodePudding user response:
the diff from Aug-18 e now Dec-8 in month is 3 .. until the Dec-18
select '2021-08-18',CURRENT_DATE(),TIMESTAMPDIFF(MONTH,'2021-08-18',CURRENT_DATE()) as monthDiff;
could be you are looking for th diff between month
select '2021-08-18',CURRENT_DATE(), month(current_date()) - month('2021-08-18') monthDiff;
CodePudding user response:
Your code is right, TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another. so, your second date parameter subtracting from first parameter it return you 3. because the diff from 08/18 to 12/08 is 3 until the 12/17 it return 3 after it will return you 4.