Below is one of the versions of sql that I came up with. Note that there are many ways to do the same thing in sql, so my script may not be consistent (even to itself). Hopefully, the comments are sufficient to explain what I was doing.
set @tOffset = TIMEDIFF(NOW(), UTC_TIMESTAMP); -- get time offset from UTC set @tOffsetTrim = substring_index(@tOffset, ':', 2); -- trim off unneeded chars set @@session.time_zone = @tOffsetTrim; -- set time zone or else date1 and date2 may be different tz's set @date1 = UNIX_TIMESTAMP('2017-01-20 12:00:00'); -- Obama checks out set @date2 = UNIX_TIMESTAMP(); -- now set @dateDiffSecs = @date1 - @date2; -- difference in seconds between dates set @oneDaySecs = 24 * 60 * 60; -- seconds in a day (86,400) set @dtDiffDaysReal = @dateDiffSecs / @oneDaySecs; -- real number (float) set @daysIntTtl = floor(@dtDiffDaysReal); -- convert to whole number set @yearsInt = floor(@daysIntTtl / 365); -- will cause error (day/leap year) during leap years (not addressed here) set @daysInt = floor(@daysIntTtl - (@yearsInt * 365)); -- days remaining after years subtracted set @daysRemainder = (concat('.', (SUBSTRING_INDEX(@dtDiffDaysReal, '.', -1)))) * @oneDaySecs; -- remainder * seconds in day set @hrsMinsSecs = SEC_TO_TIME(@daysRemainder); -- built-in func to convert seconds to time set @hrs = SUBSTR(@hrsMinsSecs, 1, 2); -- get hours set @mins = SUBSTR(@hrsMinsSecs, 4, 2); -- get mins set @secs = SUBSTR(@hrsMinsSecs, 7, 2); -- get seconds select if(@yearsInt < 0, cast((@yearsInt + 1) as signed), @yearsInt) as `yrs` -- signed, in case years are negative -- , floor(@daysInt / (365.25 / 12)) as `mthAp` -- approximate number of months -- , (((from_unixtime(@date1, '%Y') - year(now())) * 12) + from_unixtime(@date1, '%m')) - month(now()) as `mths` -- counts actual months, adjust as necessary (plus or minus a month, depending on how you count) -- , @daysIntTtl as `daysTtl` -- days (without sutracting years) , @daysInt as `daysMYrs` -- daysMinusYears, days remaining after subtracting years , @hrs as `hrs` , @mins as `mins` , @secs as `secs`;