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`;
Posted in MySQL | Tagged , , , | Leave a comment

All Your Base Are Belong To Us

So, I was out riding my bike today and ran across this lovely little fellow:

Green multi-eyed creature with a long tongue wrapping around it's body.

This appeared to be painted on the utility box (not a decal). I have no idea who designed or painted this, but if you can prove it’s yours, I’m more than willing to give you credit. Fairly cool, by the way!

After thinking about it a while, I believe I’ve seen this on a sticker (or somewhere) before. However, this was hand painted, and not a mass produced sticker (or the likes).

Posted in Art | Tagged , , , , | Leave a comment

Cigarette Butts In The Wild

So it was pretty windy yesterday, and while riding my bike (undisclosed location) I noticed two cigarette butts (one white, one undisclosed color) running for their lives (as though the wind was chasing them). I wanted to ask them where they were going, but they were in such a hurry, I thought it best not to interrupt them. Born free …

Posted in Off Thoughts (Off as in Not Quite Right) | Tagged , | Leave a comment

No WMC No Windows 10

With a push to get everyone back on the Microsoft wagon, Microsoft is offering Windows 10 for free to Windows 7 and up users. I was initially excited while simultaneously worried. I mean, nothing in this life is free. There’s a catch. You know it, I know it. However, I was still on board for the upgrade until I found out that Windows 10 will not contain Windows Media Center.

I guess I’m one of the few to use it with a cable card and decoder box (to match my cable provider). I’m not sure why it didn’t catch on (wasn’t more popular). I can’t see it being the price, because after the initial investment in the decoder box (about $100.00), the cable card only costs about $2.00 per month to rent. That’s one-fifth the cost to rent a cable company converter box (so that means I broke even around a year after purchase). I can’t imagine it’s the lack of features. I can set up my favorite shows to record automatically (series record), watch live TV (don’t do that very often–can’t stand commercials), search for shows to record via search or by manually perusing the station listing (up to 2 weeks in advance), as well as watch streaming TV from the likes of Netflix. Too difficult to set up for most, I guess. It’s too bad.

Anyway, to the point, no Windows Media Center in Windows 10, no Windows 10.

Posted in Complaints, Computer Stuff | Tagged , , , | Leave a comment

Working With XML/XSLT, JSON, And PHP

So, this tale, which for your sake I will attempt to make brief, is typical of my experiences in web development. As one of my old bosses used to say (and I’m paraphrasing), “if there’s a hard way to do it, you’ll try that way first.”

To begin, I have as my personal homepage, a calendar page that is scripted with PHP and MySQL. The calendar not only displays date information, but also displays things like birthdays, Sun and Moon information, bills, and a to-do list. As I constantly visit weather sites I decided it was time to bring the weather information to my calendar. Searching an API link site I found that the NOAA provides just what I needed: NOAA Weather API. While it was fun setting up the SOAP call, the resulting information (returned) was in XML format. Not the biggest fan of XML, but it’s what we’re given to work with in this situation.

Fast forward through scripting the SOAP call, caching data locally for X amount of hours (NOAA would appreciate not being queried over once per hour), writing the initial XSL for XSLT, and wrapping it as an object for display in my calendar. Everything seemed to be fine till I decided to look at my calendar with my smart phone. Turns out the smart phone (an old one) running Android 2.2.2 wouldn’t translate the XML in it’s built in browser. I have no idea if newer phones will deal with XML (maybe with a different browser?), but that didn’t matter to me, it has to run everywhere.

Researching the subject someone pointed out that whenever possible all XML transformation should be done server-side. I understood what he was talking about, browser compatibility issues being the bane of every web developer, but that adds to server-side load, not to mention the fact that if done server-side, there’s not much advantage of using XML in the first place (for information transmission). One of the perks (perquisites) of transmitting data in XML format is being able to display it in virtually any manner with a simple change of XSL (because all of the data is already there).

Okay, so now I’ve set out to do the transformation server-side. Great. Not that big a deal. The one sticking point was that I needed to include the following line (which took some research):


<xsl:output method="html"></xsl:output>

That and a couple other changes to my PHP script, and everything was being delivered as (X)HTML to the browser. Even works on my cell phone. Great, score one for me!

A while goes by and I decide to change the SOAP call to get 12 hour data as opposed to 24 hour data. That would help to narrow down when rain, for example, was to be expected. This turned out to be a lot more work once I jumped back into the XSL(T). You see, the data is presented in 12 hour blocks, sequentially, in one section, day – night – day – night, etc., and grouped in another, high temperatures (group one) and low temperatures (group two). While this may not have presented a problem to someone more adept with XSL, it was a stumbling block for me, to say the least. After beating my head against the wall for some time, not sure how much exactly, I did get it to work … mostly. And to be sure, had I not decided it simply was not worth my effort, I would have hammered out the remaining issues. However, it suddenly dawned on me, to heck with XML, convert it to an array, and then I can do whatever I want in PHP.

Initial searches turned up suggestions to turn it into JSON. I was okay with that. I mean, javascript is cool, and besides from there I could convert it to an array if I still wanted. Unfortunately, some of the data I needed was in attributes of the XML, which simple code, like below, would not get. Mostly worked.


$xml = simplexml_load_string($xml_string);
$json = json_encode($xml);
$array = json_decode($json,TRUE);

Further research and several failed attempts (read that as code did not work) and I happened across a PHP class at: XML To Array With PHP which works. Include the class and add the following, and I was off to the races.


$weatherArr = new simpleXml2Array($weatherXML, null);
$arr = $weatherArr->arr['data'][0]['parameters'][0]['content'];

$arr now held everything I needed and could be dealt with in a straight forward fashion. Well, to be fair, straight forward to me.

tldr; XML is overly complicated and is not as useful as was originally intended (my opinion). XSLT should be done server-side. Better yet, convert XML to JSON or an array (for PHP consumption) and life will be much easier.

Posted in PHP | Tagged , , , , , , , , , , , , , , | Leave a comment

Rainy Day Fun With Photoshop

So, it’s raining off and on, and taking a moment’s break from my usual tasks I decided to do something different. Looking at the weather map to see the rain occurring in the area always shows blank corridors. Obviously, these blank areas are either deliberate or caused by obstruction (radar being line of sight and all). Anyway, a screengrab and a couple stroked paths later and I have this:

Map of Long Island and parts of New York, Connecticut, showing cloud cover as depicted by radar

Triangulating radar station location using Photoshop

A quick look at google maps and the location appears to be the NOAA station here:

Closeup map view of Long Island centering around NOAA

NOAA as per Google Maps

Fun stuff!

Posted in Fun With Photoshop | Tagged , , | Leave a comment

Arrival At Pluto

New Horizons is scheduled to arrive at the PLANET Pluto (don’t give me any of that dwarf or minor crap) on:

select DATE_ADD(date(‘2015-06-02’), INTERVAL 42 DAY) as ‘Arrival at Pluto’;

Kind of sad that I thought it would be easier to write the query than count the days manually, huh?

WordPress likes to help by adding weird apostrophes, so:

select DATE_ADD(date('2015-06-02'), INTERVAL 42 DAY) as 'Arrival at Pluto';

This yields 2015-07-14

Posted in MySQL | Tagged , , , , , | Leave a comment

Plumber Truck Graphic

Today a plumber’s truck stopped at a neighbor’s house. The graphic on the back door sent my mind into overtime (as per usual). Here is the original:

A man sitting on a toilet with a smokestack coming out of the back

Plumber Truck Graphic – Original

Here is one of the ideas that popped into my head:

Man sitting on toilet with cat on head, smokestack coming out of back of toilet

Plumber Truck Graphic – Photoshopped

I had other ideas, but I haven’t gotten around to making them yet. Photoshop, you gotta love it!

Posted in Fun With Photoshop | Tagged , , | Leave a comment

The Divergent Series: Insurgent Review

I finally got around to seeing the movie today (probably one of the last to see it at the theater). To be honest, it surprised me that it was in the theaters so long. I guess it struck a cord with some people. I guess.

Anyway, I could make a long list of issues I had with the movie (the script), but I’ve condensed it down to this:

  • Nothing against the script in this one. Just thought it was worth noting. Anyway, Leonardo can finally rest in peace as Kate didn’t live long enough (this time) to throw any more diamonds into the ocean. Score one for the men!
  • The smart ones (erudites), really? How smart can they be to let the divergent’s brother into the control room.
  • The smart ones, again. Letting the conniving dauntless into the control room? If the movie had run for another 15 minutes, I’m sure he would have flipped sides again.
  • Who didn’t see the “let’s get intimate so I can escape while you’re sleeping” scene coming? Talk about predictable (as well as stereotypical).

Seriously, did the person that wrote this just give up or go missing (thereby forcing the movie execs to use a homeless person to finish writing the script)?

In spite of my gripes (and there are more that I shan’t list), I enjoyed the movie. Shouldn’t win any awards, but entertaining.

Posted in Movie Reviews | Tagged , | Leave a comment

Having Conversations With Javascript

Have you ever wanted wanted to animate inanimate objects? I think we all have. Well, that’s a lot of work. A cheat that you can employ, however, is relatively easy with javascript and or jquery. No, I’m not talking about jquery’s animate function, which moves an image across the screen a certain distance on an event. To be sure, that could be used additionally. Btw, that’s one of the ways that I played with to animate Chuckman flying (similar to what you see at the top of this blog).

Maybe I should define what I mean by animation? In this instance, I am talking about the illusion of a conversation taking place between two snails. To better understand, click here. The default conversation that displays at that link can be changed. Click here to see a different conversation, or click here for yet another.

If you examine the links, you should observe a pattern. First, you need the base url. In this case that’s lektrikpuke.com/additional-pages/snailsInTheRain.php. That needs to be followed by ?converse=. Then enter a command separated by a ^ from the parameter to be passed in. That’s pretty much it. Note: This script as yet is not bullet proof, meaning you can make it fail miserably. As this was just a fun project for me I may continue working on it, but no promises.

Commands that are currently present:

  • dImg – short for display image
  • dTxt – short for display text

Images available:

  • im1 – short for image one, which is the bubble that appears to issue forth from the left snail
  • im2 – short for image two, which is the bubble that appears to issue forth from the right snail
  • im3 – short for image three (the background image, calling this is a fun way to show the fragility of the script)

Format for use is command^parameter^command^parameter, etc., so dImg^im1^dTxt^Say something

In the above example we call the function dImg and pass it im1 (dImg(im1)) which overlays the bubble on the base image. We then call dTxt and pass it ‘Say something’ which prints ‘Say something’ over the bubble. Note: Don’t worry about the %20’s if you’re looking at the link. Percent 20 (and the likes) is just a web browser’s way of keeping spaces out of a url (link). Your link will work with spaces, and if you copy and paste it somewhere, you may find that %20’s mysteriously showed up on their own.

The script is commented, so if you want to see the inner workings, just look at the page source.

Things on my to do list include:

  • add a dynamically generated form for image/text addition
  • add character limit to conversation blurbs
  • add parsing and overriding of timing
  • add start/stop
  • maybe add navigation/image jumping

Have fun talking to yourself, I mean making the snails talk. =)

Note: It should be obvious that different functions could be written in the same way, allowing for instance, sound files to be played in conjunction with the images. Very interesting!

Posted in jQuery | Tagged , , , | Leave a comment