[Bug] TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) get wrong result

[alanredsheep]

Search before asking

• I had searched in the issues and found no similar issues.

Version

0.15

What's Wrong?

# doris 0.15
# timestampdiff between 1981-09-11 and 2022-04-28 should be 40 years
SELECT 
 TIMESTAMPDIFF(YEAR,DATE('1981-09-11'),'2022-04-28') AS `date-str`,
 TIMESTAMPDIFF(YEAR,'1981-09-11','2022-04-28') AS `str-str`,
 TIMESTAMPDIFF(YEAR,DATE('1981-09-11'),DATE('2022-04-28')) AS `date-date`,
 TIMESTAMPDIFF(YEAR,'1981-09-11',DATE('2022-04-28')) AS `str-date`

# while the result is 
date-str	str-str		date-date		str-date
41		40		41			41
	
# timestampdiff between 1981-04-11 and 2022-04-28 should be 41 years
SELECT
 TIMESTAMPDIFF(YEAR,DATE('1981-09-11'),'2022-04-28') AS `date-str`,
 TIMESTAMPDIFF(YEAR,'1981-09-11','2022-04-28') AS `str-str`,
 TIMESTAMPDIFF(YEAR,DATE('1981-04-11'),DATE('2022-04-28')) AS `date-date`,
 TIMESTAMPDIFF(YEAR,'1981-04-11',DATE('2022-04-28')) AS `str-date`

# while the result is 
date-str	str-str		date-date		str-date
41		40		41			41

What You Expected?

timestampdiff between 1981-09-11 and 2022-04-28 should be 40 year
timestampdiff between 1981-04-11 and 2022-04-28 should be 41 year

Anything Else?

This also happens when unit is MONTH !
And it even get different result using TIMESTAMP(expr) instead of DATE(expr)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct