Robert Eisele
Systems Engineer, Architect and DBA

Calculate date ratios

When I celebrated my birthday, I got the idea that there must theoretically be a date on which two peoples ages are in a certain ratio. The basic idea were born when I thought that there must be a date on which my mother is exactly twice as old as me. Actually, this is the point of time when I'm as old as my mother was, when I were born. That means

sondob + motherage - sonage

More generally, we seek the ratio of two period-deltas. The formula to calculate the relationship between me (1988) and my mother (1962) might look like this:

x - 1988   1
-------- = -
x - 1962   2

... where x is the time we are looking for. If we express the equation just with variables, we get the following:

x - A   m
----- = -
x - B   n

Now we transpose the equation for x:

    nA - mB
x = -------
     n - m

Plug in the values from above:

   2(1988) - 1(1962)
x = ---------------
        2 - 1

and we get:

x = 2014

Okay, we let the computer specify the result a little. In the example of me and my mother, we throw the values in a simple query and let MySQL do the job for us by using the number of days to a DATE value instead of using the year.

SELECT FROM_DAYS(TO_DAYS('1988-01-23') * 2 - TO_DAYS('1962-03-06'));

Thus, it follows that on 12/11/2013 my mother is exactly twice as old as me. For the general use I've just written a stored function for that job:

CREATE FUNCTION date_find_ratio(a date, b date, n tinyint, d tinyint)
  RETURNS DATE DETERMINISTIC
RETURN FROM_DAYS((TO_DAYS(a) * n - TO_DAYS(b) * d) / (n - d));

You might also be interested in the following

 

Sorry, comments are closed for this article. Contact me if you have an inventive contribution.