Skip to content

Instantly share code, notes, and snippets.

@milimetric
Last active October 15, 2015 20:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save milimetric/b3f3d34d8d6a77f28463 to your computer and use it in GitHub Desktop.
Save milimetric/b3f3d34d8d6a77f28463 to your computer and use it in GitHub Desktop.
# 20 days in minutes / 10 minutes = 2880 chunks
# 1 day in minutes / 10 minutes = 144 chunks
use log;
set @start = '20151014050000';
select seq.tenMinuteChunk
from (select left(date_format(date_add(@start, interval seq*10 minute), '%Y%m%d%H%i'), 11) as tenMinuteChunk
from seq_0_to_144
) seq
left join
(select left(timestamp, 11) as tenMinuteChunk
from <<Pick a few specific tables to check for holes>>
where timestamp >= @start
group by tenMinuteChunk
) navi on navi.tenMinuteChunk = seq.tenMinuteChunk
where navi.tenMinuteChunk is null
;
var minutesMissing = [/*paste output of query above here*/];
var last = 0, i, lastGap, newGap, gaps = [];
for (i=0; i < minutesMissing.length; i++) {
newGap = minutesMissing[i] - last;
if (newGap > 1 && newGap !== 5) {
gaps.push({ size: 1, first: minutesMissing[i] });
} else {
lastGap = gaps[gaps.length-1];
lastGap.size = lastGap.size + 1;
}
last = minutesMissing[i];
}
var msg = '';
for (i=0; i < gaps.length; i++) {
msg += gaps[i].first + ' for ' + gaps[i].size * 10 + ' minutes\n';
}
console.log(msg);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment