Home > Net >  TIMESTAMPDIFF mysql function
TIMESTAMPDIFF mysql function

Time:12-08

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 ?

dbfiddle

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.

  • Related