Smart data conversions with regular expressions

Recently I had a side project where I repeatedly needed to manually run an SQL query based on some data received from a third party. They would send me the data containing a list of secure codes, and I would have to include those codes in a query to find matches in my database, like so:

The problem was - the third party would send us codes as a single code per line in a text file, as so:

AABBCCDDD

BBCCDDEEF

A0B2C3D45

9G8H7I6K5

etc…

No delimiters or commas etc. And there were sometimes hundreds of lines per file that I had to manually run down and place an ‘ (single quote) at the start, and a ‘. (single quote and comma) at the end. Line. By. Line.

This was getting tiresome to do several times a day, each time a fresh file was sent to me. There must be an easier way. Turns out there was - Regular Expression Searching!

Now what I can do is open the file in my favourite editor TextMate. Then I can go to Edit -> Find. Ensure that the ‘Regular Expression’ checkbox is ticked, then enter in ^ as the ‘Find’ string (RegExp for ‘Start of Line’), and a ‘ (single quote) as the ‘Replace’ string.

Then do another Find, and use $ as the ‘Find’ string (RegExp for ‘End of Line’), and a ‘, (single quote and comma) as the ‘Replace’ string.

Voila! Done. The codes will now all be delimited. All that you need to do is to delete the trailing comma from the very last code, and then you can cut and paste the contents of the text file into the IN clause of the SQL query and run it instantly!

The server upgrade is.... when??

Like every other SaaS, we like to keep our server infrastructure at HR Partner nice and updated. However, doing that can interrupt your users, so it is polite to warn your users that there will be some sort of downtime during the maintenance window.

Almost every other online site does this, and so we use the excellent Tooltip.io service to post messages to our users from time to time.

One problem. On almost every message I’ve seen on other services - they will post the time of the outage in either GMT/UTC, or their local time zone. My first question is ALWAYS - “When will this actually impact ME, where I am right now??”.

Wouldn’t it be nice, I thought to myself, if there was a website out there where you could punch in the time in GMT… actually, not even punch it in, but pass a parameter to the website with the UTC time and get it to calculate what your local time will be based on your browser settings?

I hunted around Google, and I even asked on Twitter, and while there are a ton of sites our there that can do time conversion for you, not of them (a) are particularly easy (b) can accept URL parameters (c) are free to ad and other rubbish screen clutter making it impossible to work out how to use the site.

I just wanted something simple, that just worked.

So, I decided to build my own. LocalTimeZone (localtime.zone) was born. I spent about an hour whipping up a quick, simple, one page web site that could take an ISO8601 formatted date as a URL parameter, and display the local time based on the browser/PC settings of the person who called up that page. Try it out for yourself:

https://localtime.zone?utc=2020-05-23T14:30

This should show you what the 23rd of May 2020, 2:30pm will be in your local timezone. Try it with any ISO8601 formatted date ( YYYY-MM-DDTHH:MM ).

So now on our server messages, we include a little link which lets the user see what the outage time will be in their own timezone:

Clicking the link will instantly open up a new browser window showing them the outage time in their own timezone:

The page is a simple Tailwind CSS based document with a tiny bit of Javascript based on the Luxon time module. The whole thing (registering the domain name, creating the page, testing it, uploading it and setting up a CloudFront hosting with SSL certificate) took just over an hour.

Feel free to use it yourself ‘as is’ by pointing your server outage message links to this page, or else feel free to pinch the code and use it on your own hosting platform or within your website.

Coding in silence

This Twitter poll I came across today got me to thinking. I know Kelly asked about podcasts here, but I have a lot of friends and colleagues who listen to music while working.

Here is my secret. I can’t.

Those of you who know me are probably saying “But you LOVE music! Heck, you played in several bands, you have a son who is a rising musician, you create and produce music on a regular basis, you tour the world to see bands and musicians in action regularly… How can you not listen to something that is so close to you while you work??”

The honest truth is that I love working in complete silence. Music and talking is a complete distraction for me when I am in a flow state.

I think it is probably the same reason that the poll responders above cannot listen to a podcast while working. To really get the most out of a podcast, you need to actually listen to the words and ideas being put forward by the speaker. It is of no use as just background noise. You might as well be working in a coffee shop if that is what you need (or use a background noise generator).

For me, listening to music is a bit like that. As a former keyboard player and guitarist in a band, I find that when listening to music, I go a bit deeper. I will generally isolate one particular instrument in the mix, even to the extent that I imagine playing that instrument in real time. My musician brain is constantly questioning note choices or picking out interesting chord changes or progressions. My producer brain is thinking about how everything is sitting ‘in the mix’ and listening for reverb and delay tails. There is a whole process of analysis that kicks off in my brain that means the music is a really interactive experience for me, and not just a soothing background lull.

For that reason, I cannot hold a complex programming concept in my brain or wind my way through complex logic problems while listening to music of any sort. It seems the synapses in my brain can only deal with one set of complex universal puzzles at a time.

All luck to those of you who can do both though. My own sons seems to traverse the world these days with earphones in and listening while interacting with other or carrying out day to day tasks. Sometimes I envy them. Often I don’t.

Side motivation

I recently obtained a second 27” monitor for my iMac. This one is off to the side and rotated 90 degrees so that I can look at long bodies of computer code easily without scrolling up and down.

I also use the ‘Mission Control’ feature on macOS, which allows me to have several desktops preconfigured, and to jump between them as needed. I have Spaces set up for communications, programming and web browsing.

On the communications and programming spaces, I have apps set up on both screens for maximum productivity. But on the web browsing space, I used to have the second monitor showing my social media pages while I browsed other pages on the main monitor.

This was less than optimal, because I would routinely find myself getting distracted by my Facebook or Twitter feed all the time. The question was - what to put on that second monitor as a semi-permanent display.

The answer was actually pretty simple. I now have the page for my SaaS HR Partner showing on that screen all the time when I am browsing the web. I absolutely LOVE what our UX designers have done with the website, and I enjoy looking at it all the time. Additionally, I enjoy looking at the customer testimonials and new customer logos that are getting rapidly added to that site. It is hugely motivating and helps me to stay focused on our mission of building the best possible HR system for small to medium businesses worldwide.

What is your ‘go to’ static display screen that you would like to have in the background all the time?