Setting up a daily status record in MySQL

Phases of progression of a full lunar eclipse by Jean Beaufort

Say you have a table in your DB and and the way it’s set up is as a static “current state” rather than as a timestream. The following is example data from a table called towns

towns has an id, a name, an integer (0 or 1) representing blocked status and a planning stage string.

In this kind of setup, when a status parameter changes, it changes forever and the previous status is lost.

Here’s how you can make a daily status record so you can look back and see the history of an item’s status.

1. Create a stored function to figure out the status

If status is very obvious, you may not need this step. However, as data gets more complicated, you may find that status isn’t straight-forward. For the towns table above, the following conditions apply to work out the status:

  • The town can be blocked or not blocked.
  • The possible states for planning_stage are “pending”, “audited” or “complete”

The possible statuses follow (these examples are all made up so don’t worry too much about it making sense)

The stored function is going to take the town_id and return a string with the status. We’re also going to add a status “UNKNOWN STATUS” to catch cases that don’t fit the requirements and a “NO SUCH TOWN” status in case the town_id passed in is bogus.

drop function if exists get_town_status;
DELIMITER //
CREATE FUNCTION `get_town_status`(input_town_id int(10)) RETURNS varchar(31) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE town_status varchar(31) charset utf8;
  DECLARE town_exists, is_blocked, is_pending, is_audited, is_complete int;

select town_id,
       blocked,
       if(planning_stage="pending",1,0),
       if(planning_stage="audited",1,0),
       if(planning_stage="complete",1,0)
into town_exists, is_blocked, is_pending, is_audited, is_complete
from towns
where town_id=input_town_id;

set town_status = case
  when town_exists is null then "NO SUCH TOWN"
  when is_blocked=1 and is_pending=1 then "Blocked pending approval"
  when is_blocked=0 and is_pending=1 then "Ready for audit"
  when is_blocked=1 and is_audited=1 then "Blocked during audit"
  when is_blocked=0 and is_audited=1 then "Ready for certification"
  when is_blocked=1 and is_complete=1 then "Refused"
  when is_blocked=0 and is_complete=1 then "Certified"
  else "UNKNOWN STATUS"
end;

RETURN town_status;
END //
DELIMITER ;

We can then use it like

select get_town_status(123456789), get_town_status(36972), get_town_status(63406);

Which returns “NO SUCH TOWN”, “Blocked pending approval”, “Ready for certification”

2. Create a status record table

CREATE TABLE town_status (
   town_id int(10) unsigned NOT NULL,
   town_status varchar(31) CHARACTER SET utf8 DEFAULT NULL,
   recorded_at date NOT NULL,
   PRIMARY KEY (town_id,recorded_at)
) 
;

This sets up a link between the town and the status on a particular date. The primary key is a combination of the town_id and the recorded_at date, meaning you can only have one record for each town on any given day.

You’ll note that although town_id is an id from a foreign table, it doesn’t have a foreign key constraint. This is because even if the town is deleted, you will still want to enter an historic record of its final stage.

The initial population of the table will simply add today’s values for all towns to the status table.

insert into town_status (select id, get_town_status(id), curdate() from towns);

3. CREATE A current status view

The current view will show the latest recorded status for each town. This view will self-join the table town_status finding the latest_date for each town.

This view will be useful if you want to quickly get the current recorded status of all your towns (for example if calculating it using get_town_status is spendy).

create view current_town_status as (
    select ts.*
    from town_status ts
    join (
        select town_id, max(recorded_at) as latest_date
        from town_status
        group by town_id) x 
    on x.town_id = ts.town_id and x.latest_date = ts.recorded_at
)
;

4. make a daily event to store the status each day

We’ll make an event to recur at 00:15 every day, starting tonight. This event will record the status for each town if it has changed since the most recently stored value. Two caveats apply: 1) we’ll need to add a new row if the town is a new entry into the towns table and 2) we’ll also need to record if a town disappears from the towns table.

The left join and null check in the first insert statement will take care of new towns: if the town is not already on the town_status table, it will be added with its status.

To manage the second case, where the town has been deleted from the towns table, we check to see if there are any towns on the town_status table but not on the towns table, where the last recorded status was not “NO SUCH TOWN”. If any examples are found, then add a “NO SUCH TOWN” status for this town_id for today.

#Nightly event to keep track of town status changes
drop event if exists record_town_status;
delimiter |
create event record_town_status
on schedule
every 1 day
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 15 MINUTE)
do
begin

insert ignore into town_status (
    select t.id, get_town_status(t.id), curdate()
    from towns t
        left join current_town_status ts on t.id=ts.town_id
    where (ts.town_status is null or get_town_status(t.id) <> ts.town_status)
)
;

insert ignore into town_status (
    select ts.town_id, "NO SUCH TOWN", curdate()
    from current_town_status ts
             left join towns t on t.id = ts.town_id
    where t.id is null and ts.town_status <> "NO SUCH TOWN"
)
;

END |
delimiter ;

Over time, the town_status table will build and you will be able to see a history of events. E.g.

select * from town_status where town_id=43624;

Might return something like

43624 Blocked pending approval 2020-06-01
43624 Ready for audit 2020-06-30
43624 Ready for certification 2020-07-01
43624 Certified 2020-07-06

And then you’ll be able to things like analyse how long it typically takes to move from stage to stage.

The same principles apply for statuses that need to be recorded more or less frequently, you would just need to adjust the time unit in the status table and the event timing. Beware unchecked growth where statuses change frequently and your initial data table is very large.

(FYI: My DB is MySQL 5.7 so doesn’t support window functions)

Google Data Studio: filtering times to the past hour (you’d think it’d be easy)

I am within 3 weeks of first meeting Google Data Studio (DS). I am head of data at a small firm that was subsumed into a larger firm last year. Now that the initial merge issues are clearing, we’re getting down to the brass tacks of making our very different technologies work together. They’re very google-oriented – lots of BigQuery & Go and the data bods use R. I’m more MySQL and Python myself. We didn’t have a regular BI tool, hence why I’m getting up to speed with DS atm. DS gives a lot of bang for your buck (it’s free!), but has some interesting quirks.

I’ve had a few other battles with DS already, hopefully I’ll get to expand on those in further articles. Some of these are real head-scratchers; things you would think are such obvious requirements that the lack of them certainly makes me think my dearth of experience is the issue. Such a one seems to be the inability to filter values by time. An initial internet search turned up a couple of hits, but nothing really concrete:


So! How I can filter a set of timestamped results to show only values from the past hour?

This is the data I’m working with: the first column is a timestamp incrementing every minute and second column is an integer.

I’ve inserted a Chart > Scorecard and I want to sum up the values in column 2 if the time in column 1 falls within the past hour.

Data Studio Scorecard in edit mode

DS has grand tools if I want to filter by the date:

The image shows a configurable date picker – showing is a fixed start date of Jan 1st 2020 and an end date of now.

But it seems only to let you set the date, the time of day is not adjustable. Minimum “Unit” granularity is “day”; even when my data type is Date & Time or Date Hour Minute.

Okay, so now what? I’ll try to make a different kind of filter – based on the time value

A screenshot of a filter creation from DS

So, it starts out looking hopeful, I can choose the time metric and only include times that are later than a date and time I pick! But on closer inspection, that’s a fixed time, not a relative one, so no dice. Clicking around, nothing else in the filter allows relative comparison, so I’ll have to think of something else.

Hmmm. What about if I set an is_in_past_hour boolean? I’ll make a new calculated field. Oh, and by the way, DS doesn’t do IF statements. It’s case or nuffink.

My first attempt – apparently this is hideous. Have you seen all the limitations of case statements?

After a bit of jiggery-pokery, including

case when datetime_diff(current_datetime(),the_time,HOUR)=0 then true else false end

erroring with Invalid formula – Invalid input expression. – WHEN conditions must compare a dimension or metric with a literal value.

I ended up ditching the case statement altogether and made an hours_before_now variable instead. I will filter results so that only results where this variable is 0 are included.

datetime_diff(current_datetime(),the_time,HOUR)

Filtering the hours_before_now variable to 0

Phew. All done. But hang on, why are the results still not matching what I’m expecting?

Rounding.

It’s rounding half hours up or down.

Le sigh.

So I switch all the references in my filters and variables from hours to minutes and check there are fewer than 60 of them.

Change the variable to use minutes
Change the filter to allow < 60 minutes

Finally, this works. I’m getting the same value in the DS report as this simple wee query in MySQL:

select sum(my_value) 
from my_table 
where the_time >= date_add(now(), interval -1 hour)
;

Time (out) for a coffee.

StuffGoesWrongException handling for today

2017

Blah blah, been away, insert excuse here.
I’ve had a fab Christmas, I hope you have too. I returned to full-time work recently and have been too wiped to enjoy my fam let alone anything else. But over crimbo I finished a pair of socks – no languishing on second sock for me, just whip, whip, go. 

They’re patterned, and done in variegated, so of course the patten is indecipherable. They’re “To the ocean” by Helena Callum from knit now 60. I only made one mod- to slip the travelling stitch every other row.

My bro got me knitting stuff from Winnie’s craft cafe for Christmas, including this gorgeous yarn bowl by carol burke. I <3 it muchly.


I’m def in a sock mood and lil bro hit the nail o the head with his gift cos the other things were sock yarn and a sock book. I’m thinking of joining the 12 socks in 2017 challenge set out by Dee Murphy in the DKC knit along group on facebook. Just to be clear, I’m taking that as 12 individual socks, not 12 pairs as srsly? Wrists aren’t cut out for that amount of action!!

Have started a wee leftover mitred sq blanket tho.

 All these shots are brought to you courtesy of night-time lighting soz.

What else? Hmmm my resolutions are the same as always and I expect they’ll all do what they usually do and fizzle out by next week. Except one. Apols for the cryptic stuff that follows. I’m doing something that’s making me unhappy ATM and I’m working on digging my way out of that. My resolution for January is to study up for the first, best opportunity to get out. Happens in Feb. Fingers crossed.

Honeycomb recipe

 Honeycomb’s kinda my thing, I make it and bring it to shindigs, or pop it in a takeaway box if I want to make a quick gift. It’s very easy, except for one thing, which is catching it at the right moment – after the sugar melts but before it burns. If the sugar is even an eensy teensy bit caught, the honeycomb will go right over, because as it foams upon addition of the bicarb, it gets even hotter inside, or traps the heat or something.

I was at a wonderful studio warming party yesterday hosted by Renee Callahan (East London Knit). I brought some honeycomb and exclaimed how easy it was to make but failed to give  any details. Hey, it wasn’t the only bubbly stuff consumed that afternoon, ‘k?

So my apologies for teasing, and here it is:

200g white caster sugar (brown sugar won’t work)

50g golden syrup (measured by weight straight into the saucepan on a scales – spoons are kinda pointless with golden syrup)

2 teaspoons bicarbonate of soda

Important: silicon bakeware to put it in (it’ll just stick to tins and glass) do not butter or otherwise prepare

1) melt the sugar and syrup together over a medium to hot heat and until it all starts to bubble briskly – I don’t think you’re supposed to stir but I do anyway

2) turn the heat down so it’s still bubbling, but more slowly. Let it completely go to liquid, it should be a lovely clear golden colour by now. 

3) take it off the heat, rapidly stir in the bicarb, and get that stuff into the silicon bakeware as soon as possible

4) let it cool completely. Don’t prod or poke at it. Don’t even pick bits off the side. Go lick the spoon instead. (Seriously, it could collapse)

5) try to break it into chunks without touching it. Baking parchment rocks, plastic bags are bad (sticky!!!!)

6) eat same day or maybe day after, and don’t get it wet or feed it after midnight.
:) xxxxx e enjoy!

Episode 4: Catching up with Catchloops

I was very flattered to be interviewed for the Blasta podcast. Dearbhla asked some great questions and got me thinking about the design process.

Blasta

For Episode 4, Dearbhla caught up with Elanor King a.k.a. “catchloops” when they were both at Unravel earlier this year.

2015-03-11 161 Unravel souvenirs

During their chat they speak about a number of patterns:

To celebrate her Knitty success, Elanor has set up a KAL. The cast-on date was May 1st but you have until July 20th…

View original post 247 more words

Lady Lismore

One of my patterns has made it into Knitty! And I am running a KAL with prizes!

My beautiful sister models the crescent-shaped shawlette in St. Pancras station.

Lady Lismore features a trio of wrapped-stitch stitch patterns. Wrapping a stitch is a way to make a longer-than-usual stitch. The more times you wrap the stitch as you make it, the longer it will be. On the next row, you drop the extra wraps and just work with the elongated stitch. In Lady Lismore, different ways of manipulating the wrapped stitches give different effects. Each band of wrapped stitches is separated by some gratifyingly easy stocking stitch (stockinette) sections. Increases are at the edges only, and are consistent.

The Large sample for Knitty was worked using a variegated purpley-red and an almost-completely-solid grellow.

Dragonfly Fibers Pixie 4-ply [100% Superwash Merino; 475m/4oz skeins]
[MC] Redbud; 1 skein
[CC] Weaverknits Grellow; 1 skein

The above Large shawl used up less than 100g (422yds/386m) of MC and 61g (256yds/234m) of CC.

I originally worked up the Small shawl in electric blue and shocking pink.

West Yorkshire Spinners Signature 4-ply [40% Wool, 35% Blue Faced Leicester, 25% Nylon; 400m/100g balls]
[MC] Sarsaparilla; 1 ball
[CC] Bubblegum; 1 ball

In fact the Small shawl used about 80g (350yds/320m) of MC and 40g (175yds/160m) of CC.

Now for the KAL info

If you would like to join the Lady Lismore KAL, it starts on May 1st and you can sign up here. There will be some great prizes, including yarn, pattern vouchers and a handknit Loom Band Hat by me.

Start Date: May 1st – this should give everyone enough time to source yarn and materials 
End Date: July 20th – this will give me enough time to wrap up the competition before my kids go on school holidays! 
How to enter: Sign up by introducing yourself and showing or describing the yarn you’d like to use 
Prizes: To be announced, but categories will include

  • Most “loved” – as voted by people reading the FINISHED!!! thread (automatically entered by signing up below)
  • Most “helpful” – as voted by clicking the “helpful” button on the project pages (automatically entered by signing up below)
  • First to finish – no cheating! If you’re entering this category, we need to see a pic of your unused yarn next to a date (on or after May 1st) in a newspaper or in a bank or similar (entry by posting date pic on your project page and signing up below, first to post a project with pics in FINISHED!!! thread wins)
  • Best riff – I just love it when people take an idea of mine and really make it their own, whether by changing up the colours, changing the shape, adding beads, shells, whatever – I will pick a handful and then ask for votes (entry by signing up below and stating your intention to riff)

If this sounds like your kind of -along, then please step right this way and sign up here! I’m so excited to see what you do!

Please ask questions if you have them, pop ’em into a comment below.

Xxx Elanor

Sewing hack: measuring lots of quarter inches



If you need to pin one piece of fabric a small distance from an edge, e.g. pinning bias tape around a curve, but hate fiddling with the tape measure or ruler at the same time, mark the small distance on your thumbnail with an erasable marker and use that instead.

Visible Mending and what’s the problem with knitting top-down.

I made a school jumper 3 years ago for child#1 and it’s still in use for child #2. It was made with Debbie Bliss Cashmerino and washes quite well. It has frayed somewhat at the sleeves though.



I have learned the hard way why our ancestors worked their clothing bottom-up. If a garment frays at the hems, where it is most likely to, then the damage runs further and faster when the garment is made top-down, than if it’s made bottom-up.

I picked up stitches below the frayed area. I picked up a further 2 sts either side as well.

Then I worked in the original pattern (baby cable rib) for the same length as the cuff. I picked up a St from the jumper at the corresponding point at the start of each row. I cast off by working slip st into the cuff cast-off and slipping the previous St over. The cuff cast-off was quite firm actually, as I had replaced that recently.



Unfortunately, I don’t have any marching yarn and I had to guess at the needle size (3mm?), so this is a very visible mending. I’m quite determined that this jumper will last for as long as it fits. There may be more patching ahead! 

Honestly though, I think I have to just bite the bullet and get another ball of baby Cashmerino and rework the cuffs.