Google Assistant on Nexus 5 and Nexus 7 (so close…)

I have an ageing Nexus 5 phone. I also have a Nexus 7 (2013) which – incidentally – has been the best tablet I’ve had at a fantastic price and I’m sorely disappointed they’re not making it any more. I digress.

On clicking the link, I get asked to open in Google Assistant… yes please!

Neither of these devices officially supports Google Assistant, the fancy chatbot/AI thing from Google, which is a shame as I do have a Google Home and quite like it.

Various articles online seem to suggest both devices are too old to be supported, which – if true – would be a shame but understandable given the age.

Strangely, it seems to be almost there. It’s even ended up on my phone and tablet. If I click this link on either the phone or tablet (a Google I/O service) I am asked if I want to ‘open in Google Assistant. It seems that any clickable link (you have to click from an existing page) will open this if the URL begins

This is the resource opened in the native Google Assistant app on a Nexus 5

Not that I get far beyond that, but it’s interesting (and frustrating) to know that somewhere beneath the surface and to some degree or other, Assistant is installed.

Compressing and Storing Time Correctly

After earlier work with DATETIME and indexes, I am still seeing some performance issues with storing logs with a full timestamp. Since a lot of the reporting is based on day-on-day and week-on-week comparisons, it could be more efficient to store the date and time components separately.

For my purposes, I’m using time to a resolution of about 15 minutes so there’s no real need to suffer the extra storage and performance overhead of working with a TIME field.

Instead, I’ve opted for a TINYINT with a value between 0-95 inclusive – one for every fifteen minutes of the day. Naturally, this is indexed.

The issue comes with how to convert time into these segments, and back again. Logically, 00:00 to 00:15 should be the first segment, but I have to be careful when reporting this back.

Technically if I’m quoting the range in a graph, there’s no problem. It’s between 00:00 and 00:15. If I’m forced to plot a point – say in a graph – the easiest solution would be to drop the dot at 00:00:00. The technically most accurate would be at 00:07:30.

That could be awfully messy, but it’s the midpoint of each range and better than quoting a measurement (say, originally at 00:14:59.999999….) as 00:00:00 when it would clearly be better as 00:15:00.

In this case, I suspect simplicity prevails. Most normal people (i.e. not me) would be more comfortable seeing nice round numbers: 00:00, 00:15 and so on. Seeing 00:07:30, 00:22:30 and so on is likely to be unnecessarily confusing. Thankfully the incoming quality of data is not that precise, so nobody will scrutinise this.

Quoting the full range (00:00 – 00:15) would be more appropriate anyway, in tables and text reports.

Recent Movies

Some movies I’ve watched recently (and not so recently, but recording for sake of reminder)

Captain Phillips
Inspired by an incident in 2009, this film follows the captain of the Maersk Alabama during a hijacking and subsequent hostage-taking. Great movie, very well portrayed. Highly recommended.

Hologram for the King
Another Tom Hanks film. This one’s a bit more laid-back and tells the story of a depressed, washed-up salesman in Saudi Arabia trying to sell a videoconferencing system to the government. Mostly enjoyable with light-hearted humour, but fairly slow-paced.

Sausage Party
Finally got round to watching this on New Year’s Eve. Very rude, crude and immature, but certainly not for kids. Entertaining enough, but I’m glad we waited for the price to drop before renting.

The Lobster
I saw this a few months ago on Netflix but worth remembering. Colin Farrell is wonderful in this odd, dark comedy about a hotel of sorts for helping people to find life partners in a world where – if you remain single – you turn into an animal. Bizarre, odd, and certainly not to everybody’s tastes, but I enjoyed it.

Dark comedy set in Minnesota about a small town murder mystery and the people involved in it. A fair chunk of this movie is about the location and the relaxed nature of its population. Fun, quirky and I’m interested in seeing the TV series. On the other hand, it’s very slow-paced.

Appeared on Netflix a few months ago, so we gave it a watch. Decent enough movie and storyline with Ellen Page and Allison Janney. Warm, well-paced and interesting characters.

Keri Russell is the American girl looking for an authentic ‘Jane Austen” experience in an immersive themed vacation in England. I chose this from Netflix on a lazy day with low expectations. It wasn’t great, but enjoyable enough.

Pride & Prejudice & Zombies
Somehow I thought this would be a good idea immediately after Austenland. Stopped it after about fifteen minutes. Maybe we’ll try again but it’s as silly as it sounds, and wasn’t particularly engaging either.

Suicide Squad
Rambling and jumpy storyline, with no real background and character development. Good enough for some mindless action, but nothing captivating. On the plus side, soundtrack was great.

Jason Bourne
Something something Jason Bourne something something Treadstone. Bland and disappointing. The original was the only decent movie in this series.

London has Fallen
Gerard Butler saves the day again as London falls to a series of attacks. Lots of luck seems to help as well, but for mindless action this ticks all the boxes.

Beautifully shot. Sandra Bullock gets attacked by bits of space debris. This film inspired me to download Kerbal Space Program. Can confirm – space is hard.

The Martian
Matt Damon recreates The Good Life in space, after his fellow crew members leave him on Mars following a tragic accident. Enjoyable and witty.

Independence Day: Resurgence
It’s hard to critique an alien movie for being unrealistic, but this movie manages it with a frankly stupid plot. Will Smith added a charismatic element to the first film, which is sorely missing here.

The Secret Life of Pets
Annoying voiced creatures in a forgettable storyline. Watch the trailer to see all the funny bits.

Edge of Tomorrow
Tom Cruise and Emily Blunt on repeat. Quite a lot of fun. I enjoyed it.

Running Android apps in Chrome on Windows 10

Google has been working on a system for running Android apps on Chromebooks for a couple of years now. Since the underlying technology is roughly the same, it’s possible to run Android apps in Windows, using the Chrome browser as a host.

You will need the ARC Welder app from Chrome Web Store. I had to download this twice for it to work. The first download was large (120Mb), presumably the runtime, and didn’t seem to install properly. On the second attempt it was a more modest 12Mb, and then worked fine.

Once the second attempt is complete, a new icon appears in the apps panel (available from chrome://apps )

When launched, the app requires an Android APK (packaged app) to work. I’ve been playing with a traffic management game in development called Traffic Lanes 3. The author publishes APKs as they continue to grow the game, which is perfect here.

As it happens, I find the editor tools on this game a bit fiddly on a tablet, and prefer a mouse. The developer has stated they have no plans to produce a desktop version, and frankly if ARC Welder continues to cover this gap there seems little point.

Installing TAUCP on Total Annihilation from Steam

TAUCP is a mod for Total Annihilation (great, vintage top-down fighting game). Total Annihilation is now available on Steam for a decent price and runs well on modern Windows 10 PCs.

TAUCP comes as an executable installer. Run the installer and let it install to the default location (probably c:\cavedog\totala).

Next, open Windows Explorer and go to that location. Copy all the installed files (which will include a whole bunch of folders, ai, gamedata, maps, etc.)

Go to C:\Program files (x86)\Steam\steamapps\common\Total Annihilation\ and copy the files into there.

I got one prompt, which was to overwrite rev31.gp3, and allowed this to overwrite. This file enables the mod.

Now, when you launch Total Annihilation, the new mod kit should be active with new units in various factories.


  • It also creates a file prefrontend.exe in the install directory which allows you to configure the mods and AIs.
  • rev31.gp3 is the patch file that enables TAUCP. If you don’t copy, it won’t work. The prefrontend.exe file swaps the original and modified rev31.gp3 file as you enable the mod.

Improving boot speed of Raspberry Pi (Jessie)

A quick note for myself:

Raspberry Pi hangs on “a start job is running for lsb no limit” – this is the network interface manager trying to start up its connections. If you have interfaces listed in /etc/networking/interfaces which do not exist, you might see a timeout or delay here.

Solution: create a new file (and possibly folder if needed) in /etc/systemd/system/networking.service.d/reduce-timeout.conf


This will drastically reduce the starting timeout for interfaces. I haven’t tested in detail, but it looks like new interfaces will still load normally after boot.

Worth noting that systemd first looks in /lib/systemd/ for various default configs, then looks in /etc/systemd/ for additional configs, before merging them all. The exact chain of configs as applied can be seen by running systemctl status networking.service


Southern Rail Strikes Dates

GTR operates the Southern rail franchise in England, which covers several counties south of London. They are currently in a dispute with the RMT and ASLEF Unions over on-board roles and responsibilities.

I work with a number of town centres across the affected region and we are interested in seeing the effect of these actions on High Street performance. To achieve this, I have compiled a list of dates and sources. It will be updated as I gather more information, and I welcome contributions.

Disclaimer: This post was last updated on 6 December 2016 and is a work in progress. E&OE. It may not be current and should not be relied upon for planning or other purposes.

Apr 2016

1100 Tue 26 April – 1059 Wed 27 April
RMT strike, 24 hours – Sources: BBC; RMT Website

May 2016

Postponed 1100 Tue 10 May – 1059 Wed 11 May
RMT strike – Source: RMT Website; postponement notice

Postponed 1100 Thu 12 May – 1059 Fri 13 May
RMT strike – Source: RMT Website; postponement notice

0001-2359 Wed 18 May
RMT strike. Was Fri 20 May but brought forward. Sources: Southern Press Office 1 & 2; RMT switch notice

Brought forward 0001-2359 Fri 20 May
RMT strike. Pulled back from two previous 24-hour strikes. Source: RMT Postponement notice

June 2016

0001-2359 Tue 21 June
RMT strike. Sources: Southern press release; BBC; Twitter

July 2016

* Southern began revised timetable from 11 July 2016

Aug 2016

0001 Mon 8 Aug – 2359 Friday 12 Aug [Sources: RMT announcement and calling-off]
RMT Strike suspended at 2200 on Wednesday 10 August. Southern resumed pre-strike timetable on Friday.

Sep 2016

0001 Wed 7 September – 2359 Thu 8 September
RMT Guard strike on Southern [Source: RMT]

Called off 0001-2359 Wed 7 September
RMT Station staff strike across GTR i.e. not specific to Southern [Source: Southern; RMT calling-off]

Oct 2016

0001 Tue 18 October – 2359 Thu 20 October
RMT strike [Source: BBC; RMT]

Nov 2016

0001 Thu 3 November – 2359 Sat 5 November [Source: BBC]
RMT strike

0001 Tue 22 November – 2359 Wed 23 November [Source: BBC]
RMT Guards strike

Dec 2016

0001 Tue 6 December – 2359 Thu 8 December [Source: BBC; RMT]
RMT Guards Strike

Tue 13 December – Wed 14 December [Source: ASLEF]
ASLEF Drivers strike

Fri 16 December [Source: ASLEF]
ASLEF Drivers strike

0001 Mon 19 – 2359 Tue 20 December [Source: RMT]
RMT Guards Strike [changed date, was 22-24 Dec]

Brought forward Thu 22 – Sat 24 December [Source: RMT]
RMT Guards Strike – brought forward to 19-20 Dec

0001 Sat 31 December – 2359 Mon 2 January 2017 [Source: RMT]
RMT Guards Strike

Jan 2017

Mon 9 – Sat 14 ASLEF [confirmed ASLEF website]


Please use the comment form below. Note: This post is not intended for debate on the actions. Comments welcome for date changes, corrections and updates only. All others will be removed.

When IS the Night Time?

Seems like a daft question, but the definition of ‘Night Time’ in a city centre is all over the place. Specifically, this refers to the Night Time Economy (NTE) which businesses and place managers use to measure performance in the evening.

We need a consistent definition to compare like for like, across suppliers and organisations. Here are a few choice notes:

The ATCM Purple Flag scheme defines NTE as between 1700 and 0600.

Retail intelligence company Springboard uses 1800 to 0400.

A recent report from the Greater London Authority describes it as 1800 to 0600.

1700 was initially a little close to working hours for my comfort, but it forms part of the Purple Flag which is a well-known and widely used reference. It seems reasonable to imagine people transitioning from the workplace to night-time destinations (after-office drinks!). Indeed, workers are important contributors to the local economy so very much on town centres’ radars.

The distinction between 0400 and 0600 is likely negligible. It looks like only service and transport workers are typically active this early, and with long licensing hours I suppose there’s little to be lost from measuring all the way to 6am.

It also looks to me, from reading some towns’ NTE strategies, that the emphasis is on early evening. Given that nearly all of these are based on the Purple Flag this looks like a reasonable de facto standard definition.

Finally, the data also points to a broader definition. I have a couple of counters in areas specifically designated as NTE strongholds (i.e. streets with pubs and clubs). Taking Fridays as an example, there is a small rise between 1700 and 1800, peaking at 2300-0000, before falling by 0400. Other areas tend to mix NTE and commuters, so more difficult to see.


On CHAR, Unicode and String searching

As part of my wifi project, I need to store the hashed MAC address of devices so we can run analysis and gather daily figures. We use MariaDB, based on MySQL, for this particular application. There are various tables used for this, and all JOINed by the common hash.

When I first put the system together in haste, I used VARCHAR(20) as it wasn’t obvious at the time which route we’d go down, or how things would be processed. You can be a little more wasteful with VARCHAR as the storage space is n+1 (where n is number of chars), so the field shrinks to suit. Fixed strings using CHAR always pre-allocate a fixed space, so are less flexible.

Roll forward a year. Things have grown very fast, and we’re starting to see performance limits being hit, and storage space becoming an issue, so it’s time to review the schema.


Now we know the maximum bytes for any hash will always be four. They can be smaller than this (depends on application) but eight is the limit.

So, CHAR(8) would seem a reasonable way to store eight bytes in hex. If you’re screaming about INT/BLOB/VARBINARY storage, I’ll come back to that in a moment…

Issue one – UNICODE

First problem is that, if you use a multi-byte charset like utf-8, CHAR is potentially much worse than VARCHAR. As a fixed field, CHAR must allocate the maximum space for all supported Unicode characters, so three bytes are reserved for each character (MySQL utf8 doesn’t support supplemental unicode characters, so three is the limit).

Compare to VARCHAR, where the field size is dynamic and therefore grows according to the characters in use. For anybody using straightforward Latin characters, this needn’t be more than one byte per character.

In the case of utf8, VARCHAR can be far more efficient than CHAR.

If you’re only using basic characters or storing hex values in CHAR, explicitly set the latin1 character set on this field:

ALTER TABLE `mytable` MODIFY `col` CHAR(8) CHARSET latin1

Issue two – Comparing CHAR and VARCHAR

As I was testing this I converted some tables hoping that the comparison would be fairly straightforward, but it seems there’s a big performance hit when comparing a VARCHAR field to a CHAR field (possibly not helped by charset differences). I was seeing queries slow by a factor of 50+ in some cases.

SELECT, b.category from a inner join b on

(fk is a varchar; id is a char – both contain the same string)

This is easily solved by explicitly setting a cast on the VARCHAR field, thus:

SELECT, b.category from a inner join b on CAST( AS CHAR(8) CHARSET latin1)

Performance will be drastically improved. I believe this is due to the order and type of string comparison going on, and this addition explicitly converts all the potential strings up-front and avoids doing it on an ad-hoc basis.

Next steps

Performance is now much better, but this is still a fairly wasteful way of storing four bytes. The advantage is that, for debugging, it’s easy enough to test queries and investigate issues without having to deal with binary fields.

In terms of efficiency, the next step might be to switch to an UNSIGNED INT, which is allocated four bytes. This is readable in query output, and can be passed in the browser without too much fuss.

I suspect INT and BINARY also have further performance gains. I haven’t tested the overhead, but I suspect the collation (the system of testing equivalence in characters) is adding some work to string comparisons – none of which we need here.

I need to test further before using UNSIGNED INT, and the update process is marginally more involved as the field needs to be converted and modified.

Right now, the performance is back at decent levels, so this is probably something to park for a while!




Reporting Data with Daylight Savings Time

At the end of October it’ll be time to put the clocks back. We’ll get an extra hour in bed to compensate for the hour lost at the end of March, and the bi-annual tradition of media articles asking whether we should ditch the whole thing will run again.

For anybody dealing with dates and times, daylight savings can be a pain. If your job involves managing computers, you might well keep everything in UTC (/GMT) to save headaches. This is also sensible advice if you deal with multiple time-zones. By sticking to UTC, you avoid the annoying issue of living out the same hour twice one morning somewhere in Autumn.

In city centres, retail and business, our data is very much dictated by local time, not UTC. If the shops open at 8am, it will be based on local time. Work starts at 9 sharp, even if your previous night’s sleep was rudely shortened by sixty minutes.

For this reason, when we (meaning I – my company) report on city centre figures, we use local time. It keeps the peaks and troughs of the day in order. If we used UTC, it would be harder to compare a July day with a November day – they’d be off by an hour.

We also store data in local time, mainly because of the complexities involved in constantly switching between UTC and local time. It’s a minor issue, buts adds time to every query and makes the underlying system more complicated. Some might store in UTC, and convert. However you do this you’ll still need to decide what to do at 2am at the end of October, when time steps backwards.


For some useful observations, I needed to look for high-traffic places with a decent night-time economy. These are from Heart of London (West End):


First observation is that all the times appear to be based on local time. The general peaks/troughs appear to line up irrespective of daylight savings time. Sunday is shown in grey on these charts.

There’s not a lot to go on here, but March 2015 looks like a straight line between 1am and 3am. March 2014 has a data point in the non-existent time, but there’s a noticeable drop. By comparison, October (where we live 1am-2am twice) seems to have a bump at 3am. This is also what I saw in the 2014 data – not shown here.

Given that there’s data in the March 2014 slot, I wonder if this is being accommodated in Springboard’s stats – and if they’re similarly compensating in the early hours of October’s backwards step.

Highways England

Looking elsewhere, Highways England publishes traffic data across its network on a 15 minute basis. They also use local time – again, this makes sense as traffic demands are dictated by the clock.

In March, they simply skip over the non-existent time. 1am to 2am is missing in the data, so for one day per year there are 23 hours’ worth of records.

In October, there is something weirder. The hour is repeated, but the data is inconsistent. This is the traffic data on a section of the M25 over October, with 1am-2am counted twice. Time Period shows the end of the 15 minute section as measured; the last column shows the number of vehicles of a given length over this period.


It looks like the sensor has managed to send something across throughout the affected time, but the data is largely missing. Interesting that the time period reports as :59 seconds only in this highlighted period, and for the two records where this doesn’t happen (01:44:00 and 01:59:00) we seem to have data. I wonder if this is a bug of some kind.


These are the two main data suppliers I have an interest in, but it’d be useful to gauge feedback from elsewhere. This is a tricky issue. The ultimate goal is to show something which is meaningful to the reader, but we need to do this in a way that does not affect comparisons at the hourly level.

In one way this is a fairly moot point. The volume of traffic at 2am on an out-of-season Sunday is likely inconsequential for many. However it does raise an interesting challenge for reports and figures, and is just one of many subtleties to consider in this sort of analysis.

Final thought: is it possible that the change in daylight savings time actually attracts people?