# 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

son_{dob}+ mother_{age}- son_{age}

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

- Age calculation with MySQL
- Optimized trend analysis
- MySQL Wishlist
- Running Standard Deviation in MySQL

**Sorry, comments are closed for this article. Contact me if you want to leave a note.**