Monday, September 9, 2013

Implementing YEARDIFF

Question: How do I compute the number of complete years between two timestamps?

Answer: Call DATEDIFF YEAR and if you don't like the answer, subtract 1!

That's not as silly as it sounds. DATEDIFF has two huge advantages over a start-from-scratch-and-do-it-yourself approach:

  • DATEDIFF takes care of those pesky leap years and the funky divisible-by-4-vs-100-vs-400 rule (2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years), and

  • DATEDIFF works... it's already been tested by bazillions of developers and end users.
DATEDIFF's problem is that it computes the number of year boundaries between two timestamps, not the number of complete years. The two numbers are equal for exactly half of all possible pairs of timestamps, and DATEDIFF's answer is too high by 1 for the other half.

But wait!

There's another function you can call to determine if DATEDIFF got it right or not: DATEADD.

Yes, DATEADD... take the number of years returned by DATEDIFF and call DATEADD to add it to the first timestamp (assuming the first timestamp is the smaller one). If the result is less than or equal to the second timestamp, then DATEDIFF returned the right number. If not, then DATEADD's answer is too high by 1... hence the "subtract 1" in the answer above.

Here's the code, followed by a test using timestamps that are different by only one microsecond, as well as one year boundary:

CREATE FUNCTION YEARDIFF (
   IN @timestamp1   TIMESTAMP,
   IN @timestamp2   TIMESTAMP )
   RETURNS SMALLINT
   DETERMINISTIC
BEGIN

DECLARE @lesser_timestamp    TIMESTAMP;
DECLARE @greater_timestamp   TIMESTAMP;
DECLARE @sign                SMALLINT;
DECLARE @yeardiff            SMALLINT;

IF @timestamp1 IS NULL OR @timestamp1 IS NULL THEN
   RETURN NULL;
END IF;

IF @timestamp1 = @timestamp2 THEN
   RETURN 0;
END IF;

IF @timestamp1 < @timestamp2 THEN
   SET @sign = 1;
   SET @lesser_timestamp  = @timestamp1;
   SET @greater_timestamp = @timestamp2;
ELSE
   SET @sign = -1;
   SET @lesser_timestamp  = @timestamp2;
   SET @greater_timestamp = @timestamp1;
END IF;

SET @yeardiff = DATEDIFF ( YEAR, @lesser_timestamp, @greater_timestamp );

IF DATEADD ( YEAR, @yeardiff, @lesser_timestamp ) > @greater_timestamp THEN
   RETURN @sign * ( @yeardiff - 1 );
ELSE
   RETURN @sign * @yeardiff;
END IF;

END; 

SELECT DATEDIFF ( YEAR, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_1,
       YEARDIFF (       '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_1,
       DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_2,
       YEARDIFF (       '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS YEARDIFF_2;

DATEDIFF_YEAR_1 YEARDIFF_1 DATEDIFF_YEAR_2 YEARDIFF_2 
--------------- ---------- --------------- ---------- 
              1          0              -1          0 

SELECT DATEDIFF ( YEAR, '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_3,
       YEARDIFF (       '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_3,
       DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_4,
       YEARDIFF (       '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS YEARDIFF_4;

DATEDIFF_YEAR_3 YEARDIFF_3 DATEDIFF_YEAR_4 YEARDIFF_4 
--------------- ---------- --------------- ---------- 
              2          1              -2         -1 

Here's another test that how a straightforward call to DATEDIFF flubs a simple age calculation but gets it right when YEARDIFF handles the call to DATEDIFF:

SELECT DATEDIFF ( YEAR, '2012-07-27', '2013-07-28' ) AS correct_age,
       DATEDIFF ( YEAR, '2012-07-29', '2013-07-28' ) AS incorrect_age;

correct_age incorrect_age 
----------- ------------- 
          1             1 

SELECT YEARDIFF ( '2012-07-27', '2013-07-28' ) AS correct_age_1,
       YEARDIFF ( '2012-07-29', '2013-07-28' ) AS correct_age_2;

correct_age_1 correct_age_2 
------------- ------------- 
            1             0 


No comments: