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.<\/p>\n
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.<\/p>\n
\r\nset @tOffset = TIMEDIFF(NOW(), UTC_TIMESTAMP); -- get time offset from UTC\r\nset @tOffsetTrim = substring_index(@tOffset, ':', 2); -- trim off unneeded chars\r\nset @@session.time_zone = @tOffsetTrim; -- set time zone or else date1 and date2 may be different tz's\r\nset @date1 = UNIX_TIMESTAMP('2017-01-20 12:00:00'); -- Obama checks out\r\nset @date2 = UNIX_TIMESTAMP(); -- now\r\nset @dateDiffSecs = @date1 - @date2; -- difference in seconds between dates\r\nset @oneDaySecs = 24 * 60 * 60; -- seconds in a day (86,400)\r\nset @dtDiffDaysReal = @dateDiffSecs \/ @oneDaySecs; -- real number (float)\r\nset @daysIntTtl = floor(@dtDiffDaysReal); -- convert to whole number\r\nset @yearsInt = floor(@daysIntTtl \/ 365); -- will cause error (day\/leap year) during leap years (not addressed here)\r\nset @daysInt = floor(@daysIntTtl - (@yearsInt * 365)); -- days remaining after years subtracted\r\nset @daysRemainder = (concat('.', (SUBSTRING_INDEX(@dtDiffDaysReal, '.', -1)))) * @oneDaySecs; -- remainder * seconds in day\r\nset @hrsMinsSecs = SEC_TO_TIME(@daysRemainder); -- built-in func to convert seconds to time\r\nset @hrs = SUBSTR(@hrsMinsSecs, 1, 2); -- get hours\r\nset @mins = SUBSTR(@hrsMinsSecs, 4, 2); -- get mins\r\nset @secs = SUBSTR(@hrsMinsSecs, 7, 2); -- get seconds\r\n\r\nselect\r\n\tif(@yearsInt < 0, cast((@yearsInt + 1) as signed), @yearsInt) as `yrs` -- signed, in case years are negative\r\n\t -- , floor(@daysInt \/ (365.25 \/ 12)) as `mthAp` -- approximate number of months\r\n\t -- , (((from_unixtime(@date1, '%Y') - year(now())) * 12) + from_unixtime(@date1, '%m')) - month(now()) as `mths`\r\n\t -- counts actual months, adjust as necessary (plus or minus a month, depending on how you count)\r\n\t-- , @daysIntTtl as `daysTtl` -- days (without sutracting years)\r\n\t, @daysInt as `daysMYrs` -- daysMinusYears, days remaining after subtracting years\r\n\t, @hrs as `hrs`\r\n\t, @mins as `mins`\r\n\t, @secs as `secs`;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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?’ … Continue reading