mysqlintervalstimestampdiff

Calculate year difference between 2 dates with 0 year as 1 year

I have 2 dates to compare so I can count years between both date. If days is less than 365 days then I use interval to make it as 1 year. However I have problem if both date is similar in day for example 2019-10-15 to 2020-10-15 will be displayed as 2 years when I want it to be displayed as 1 year. Here is my code with not the result I want:

SELECT
  TIMESTAMPDIFF(
    YEAR,
    '2019-10-15',
    '2020-10-15' + INTERVAL 1 YEAR
  ) AS `year`
FROM
  dob

Result:

enter image description here

What I want is:

2019-10-15 to 2020-10-14 = 1 year

2019-10-15 to 2020-10-15 = 1 year

2019-10-15 to 2020-10-16 = 2 year

How can I achieved that? Thanks in advance.

Solution

  • Does this do what you want? It seems you're only one day out from the expected output so I just subtracted a day.

    SELECT 
TIMESTAMPDIFF
( YEAR, '2019-10-15',
 '2020-10-15' 
+ INTERVAL 1 YEAR
- INTERVAL 1 DAY ) 
AS `year` 
FROM dob