MySQL Difference Between Dates In Days, Hours, Minutes

I was researching how to make a JavaScript countdown timer for a personal webpage, because that’s the only way to go if you want interactivity (client-side scripting at its best), and I thought, ‘how can I do this in MySQL?’ Researching that a bit, I ran into a lot of similar minded queries on forums. So, I decided to write some sql.

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`;
This entry was posted in MySQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.

Enter Captcha Here : *

Reload Image