[General Accessories] kueters:
I have updated the miband.sql and miband_raw.sql from subfolder db. (some column-names has changed).

I'm using MiFit 2.1.8 with MiBand 2. And my origin_db has a new name: origin_db_1576809003 -> so please change in run.bat / .sh every "origin_db" with the new "origin_db_...". I dont know i the filename is the same at all installations.

miband.sql seems to works fine with correct data

.header on
.mode csv
.output extract.csv
select strftime('%Y-%m-%d',Date)||" " as Date,LightSleepMin+DeepSleepMin as InBedMin,DeepSleepMin,LightSleepMin,SleepStart,SleepEnd,AwakeMin
,DailyDistanceMeter,DailySteps,DailyBurnCalories
,DailyDistanceMeter-RunDistanceMeter as WalkDistance,WalkTimeMin,DailyBurnCalories-RunBurnCalories as WalkBurnCalories
,RunDistanceMeter,RunTimeMin,RunBurnCalories,(WalkTimeMin+RunTimeMin)*60 as WalkRunSeconds,
case strftime('%m',Date) when "01" then "Jan" when "02" then "Feb" when "03" then "Mar" when "04" then "Apr" when "05" then "May" when "06" then "Jun" when "07" then "Jul" when "08" then "Aug" when "09" then "Sep" when "10" then "Oct" when "11" then "Nov" when "12" then "Dec" else "1" end
||strftime(' %d, %Y',Date) as DateUS,"Daily Activity" as Activity,strftime('%H',SleepStart) as BedHour,strftime('%M',SleepStart) as BedMinute,strftime('%H',SleepEnd) as AwakeHour,strftime('%M',SleepEnd) as AwakeMinute
,strftime('%s',datetime(date,'utc')) as DateUnix
,strftime('%s',datetime(SleepStart,'utc')) as SleepStartUnix
,strftime('%s',datetime(SleepStart,'utc')) as SleepEndUnix
,strftime('%s',datetime(datetime(date,'utc'),'+8 hour')) as WalkStart
,strftime('%s',datetime(datetime(datetime(date,'utc'),'+8 hour'), '+'||cast(WalkTimeMin as Text)||' minute')) as WalkEnd
,strftime('%s',datetime(datetime(datetime(date,'utc'),'+8 hour'), '+'||cast(WalkTimeMin+RunTimeMin as Text)||' minute')) as RunkEnd
from (
select date,summary
,cast(rtrim(substr(summary,instr(summary,'"lt":')+5,7),', "st":') as Integer) as LightSleepMin
,cast(rtrim(substr(summary,instr(summary,'"dp":')+5,7),', "ed":') as Integer) as DeepSleepMin
,cast(rtrim(substr(summary,instr(summary,'"wk":')+5,7),', "dp":') as Integer) as AwakeMin

,datetime(cast(rtrim(substr(summary,instr(summary,'"st":')+5,10),', "wk":') as Integer),'unixepoch','localtime') as SleepStart
,datetime(cast(rtrim(substr(summary,instr(summary,'"ed":')+5,10),', "v":') as Integer),'unixepoch','localtime') as SleepEnd

,cast(rtrim(substr(summary,instr(summary,'"rn":')+5,7),', "cal"') as Integer) as RunTimeMin
,cast(rtrim(substr(summary,instr(summary,'"runDist":')+10,7),', "wk":') as Integer) as RunDistanceMeter
,cast(rtrim(substr(summary,instr(summary,'"runCal":')+9,7),', "dis"') as Integer) as RunBurnCalories

,cast(rtrim(substr(substr(summary,instr(summary,'"wk":')+5),instr(substr(summary,instr(summary,'"wk":')+5),'"wk":')+5,7),', "ttl"') as Integer) as WalkTimeMin
,cast(rtrim(substr(summary,instr(summary,'"ttl":')+6,7),', "runC') as Integer) as DailySteps
,cast(rtrim(substr(summary,instr(summary,'"dis":')+6,7),'}}') as Integer) as DailyDistanceMeter
,cast(rtrim(substr(summary,instr(summary,'"cal":')+6,4),', "runD') as Integer) as DailyBurnCalories

from date_data d where type = 0) order by date;

--cast(SleepStart as text)||","||cast(SleepEnd as text)||","||

.header off
.mode list
.output extract.js
.print "data.addRows([ "
select "[new Date(" ||strftime('%Y',Date)||","||cast((cast(strftime('%m',Date) as integer)-1) as text)||","|| strftime('%d',Date) ||"),"|| strftime('%Y',Date)
||","""||strftime('%Y-%m',Date)||""","||strftime('%m',Date)||","""||strftime('%Y-%W',Date)||""","||strftime('%W',Date)||","||strftime('%w',Date)
||","||strftime('%d',Date)||","||cast(LightSleepMin+DeepSleepMin as text)||","||cast(DeepSleepMin as text)||","||cast(LightSleepMin as text)||","||
cast(AwakeMin as text)||","||cast(DailyDistanceMeter as text)||","||cast(DailySteps as text)||","||cast(DailyBurnCalories as text)||","||
cast(DailyDistanceMeter-RunDistanceMeter as text)||","||cast(WalkTimeMin as text)||","||cast(DailyBurnCalories-RunBurnCalories as text)||","||
cast(RunDistanceMeter as text)||","||cast(RunTimeMin as text)||","||cast(RunBurnCalories as text)||","||
--cast(720+(SleepStart-strftime('%s',date))/60 as text)||","||cast((SleepEnd-SleepStart)/60 as text)
"new Date(0,0,"||case when SleepStart < Date then "-1" else "0" end||","||strftime('%H',SleepStart)||","||strftime('%M',SleepStart)||","||strftime('%S',SleepStart)||"),"||
"new Date(0,0,"||case when SleepEnd < Date then "-1" else "0" end||","||strftime('%H',SleepEnd)||","||strftime('%M',SleepEnd)||","||strftime('%S',SleepEnd)||")"

||"]"||case when date = (select max(date) from date_data) then "" else "," end
from (
select date,summary
,cast(rtrim(substr(summary,instr(summary,'"lt":')+5,7),', "st":') as Integer) as LightSleepMin
,cast(rtrim(substr(summary,instr(summary,'"dp":')+5,7),', "ed":') as Integer) as DeepSleepMin
,cast(rtrim(substr(summary,instr(summary,'"wk":')+5,7),', "dp":') as Integer) as AwakeMin

,datetime(cast(rtrim(substr(summary,instr(summary,'"st":')+5,10),', "wk":') as Integer),'unixepoch','localtime') as SleepStart
,datetime(cast(rtrim(substr(summary,instr(summary,'"ed":')+5,10),', "v":') as Integer),'unixepoch','localtime') as SleepEnd

,cast(rtrim(substr(summary,instr(summary,'"rn":')+5,7),', "cal"') as Integer) as RunTimeMin
,cast(rtrim(substr(summary,instr(summary,'"runDist":')+10,7),', "wk":') as Integer) as RunDistanceMeter
,cast(rtrim(substr(summary,instr(summary,'"runCal":')+9,7),', "dis"') as Integer) as RunBurnCalories

,cast(rtrim(substr(substr(summary,instr(summary,'"wk":')+5),instr(substr(summary,instr(summary,'"wk":')+5),'"wk":')+5,7),', "ttl"') as Integer) as WalkTimeMin
,cast(rtrim(substr(summary,instr(summary,'"ttl":')+6,7),', "runC') as Integer) as DailySteps
,cast(rtrim(substr(summary,instr(summary,'"dis":')+6,7),'}}') as Integer) as DailyDistanceMeter
,cast(rtrim(substr(summary,instr(summary,'"cal":')+6,4),', "runD') as Integer) as DailyBurnCalories
from date_data d where type = 0) order by date;
.print ]);

.header off
.mode list
.output minmaxtime.csv

with MyTable as (
select datetime(date,'utc') as dt
,datetime(cast(rtrim(substr(summary,instr(summary,'"st":')+5,10),', "wk":') as Integer),'unixepoch','utc') as SleepStart
,datetime(cast(rtrim(substr(summary,instr(summary,'"ed":')+5,10),', "v":') as Integer),'unixepoch','utc') as SleepEnd
,cast(rtrim(substr(summary,instr(summary,'"rn":')+5,7),', "cal"') as Integer) as RunTimeMin
,cast(rtrim(substr(substr(summary,instr(summary,'"wk":')+5),instr(substr(summary,instr(summary,'"wk":')+5),'"wk":')+5,7),', "ttl"') as Integer) as WalkTimeMin
from date_data d
where type = 0 order by date
),
MaxTime as
(
select EndTime from (select datetime(datetime(dt,'+8 hour'),'+'||cast(WalkTimeMin+RunTimeMin as Text)||' minute') as EndTime
from MyTable order by dt desc limit 1)
union all
select max(SleepEnd) from MyTable
union all
select EndTime from (select datetime(dt) as EndTime from MyTable order by dt asc limit 1)
union all
select min(SleepStart) from MyTable
)
select strftime('%s',max(EndTime)),strftime('%s',min(EndTime)) from MaxTime;

.header off
.mode list
.output app_locale.js
select "var app_lang = """||locale||""";" from android_metadata;

.header on
.mode csv
.output weight.csv

select distinct UserName,Date,round(Weight,2)
,case when User_ID = -1 then coalesce(b.Current_Val,0.0) else Target_Weight end as Target_Weight
,case when User_ID = -1 then coalesce(b.Height,0.0) else a.Height end as Height
,case when User_ID = -1 and b.Height is not null and b.Height > 0 then round(Weight*100.0*100.0/(b.Height*b.Height),2)
when User_ID > -1 and a.Height is not null and a.Height > 0 then round(Weight*100.0*100.0/(a.Height*a.Height),2) else 0 end as BMI
from
(
select strftime('%Y-%m-%d %H:%M',datetime(substr(timestamp,1,10),'unixepoch','localtime')) as Date,w.Weight
,case when w.User_ID = -1 then 'Main_User' else u.Name end as UserName
,u.Target_Weight
,w.User_ID
,u.Height
from Weight_Infos w
left outer join User_Infos u on w.User_ID = u.User_ID
where w.User_ID <> 0
) a,
(select Current_Val,Height from Weight_Goals where FUID = -1 order by Date_Time desc limit 1) b
order by Date;

miband_raw.sql delivers some strange values. but at moment i have no time to check what there happened.

CREATE TEMP TABLE _PersonParams(Height,Weight);

.read db/health.sql

CREATE TEMP TABLE _CalList(Dist,Cal);
INSERT INTO _CalList (Dist,Cal) VALUES (40.233,0.95);
INSERT INTO _CalList (Dist,Cal) VALUES (53.645,1.19);
INSERT INTO _CalList (Dist,Cal) VALUES (67.056,1.41);
INSERT INTO _CalList (Dist,Cal) VALUES (80.467,1.57);
INSERT INTO _CalList (Dist,Cal) VALUES (93.878,1.78);
INSERT INTO _CalList (Dist,Cal) VALUES (107.29,2.36);
INSERT INTO _CalList (Dist,Cal) VALUES (120.7,2.97);
INSERT INTO _CalList (Dist,Cal) VALUES (134.11,3.79);
INSERT INTO _CalList (Dist,Cal) VALUES (160.94,4.67);
INSERT INTO _CalList (Dist,Cal) VALUES (187.76,5.24);
INSERT INTO _CalList (Dist,Cal) VALUES (214.58,5.62);
INSERT INTO _CalList (Dist,Cal) VALUES (241.4,6.1);
INSERT INTO _CalList (Dist,Cal) VALUES (268.23,6.91);
INSERT INTO _CalList (Dist,Cal) VALUES (295.05,7.62);
INSERT INTO _CalList (Dist,Cal) VALUES (321.87,9.05);
INSERT INTO _CalList (Dist,Cal) VALUES (348.69,9.43);
INSERT INTO _CalList (Dist,Cal) VALUES (375.52,10.95);

CREATE TEMP TABLE _Hght(h1);
insert into _Hght select Height*0.42/100 from _PersonParams;

CREATE temp TABLE blobdata (
id INTEGER,
pos integer,
b1 integer,
b2 integer,
b3 integer
);

with hexrec(i,l,r,c11,c12,c21,c22,c31,c32) as (
select date,0,hex(data),'','','','','','' from date_data
union all
select i,
l+1 as l,
substr(r,7),
substr(r,1,1),
substr(r,2,1),
substr(r,3,1),
substr(r,4,1),
substr(r,5,1),
substr(r,6,1)
from hexrec
where length(r) > 0
)
insert into blobdata
select i,l,
case c11 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end * 16 +
case c12 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end,
case c21 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end * 16 +
case c22 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end,
case c31 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end * 16 +
case c32 when '0' then 0 when '1' then 1 when '2' then 2 when '3' then 3 when '4' then 4 when '5' then 5 when '6' then 6 when '7' then 7 when '8' then 8 when '9' then 9 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 end
from hexrec rt
where l > 0;

create index b1 on blobdata(id,pos);

.header on
.mode csv
.output extract_raw.csv
select time,description,steps
,round(iDistance,2) as WalkDistance
,case when runs> 0 then Round(((3+runs*2) * iDistance / 15),2) else 0 end as RunDistance
,case when steps > 0 then Round((weight *2.2046 * iDistance * (select coalesce(max(Cal),0.95)/coalesce(max(Dist),40.233) from _CalList where Dist <= iDistance) / 60),2) else 0 end as WalkCalories
,case when runs> 0 then Round(((3+runs*2.0) /15 * weight *2.2046 * iDistance * (select coalesce(max(Cal),0.95)/coalesce(max(Dist),40.233) from _CalList where Dist <= iDistance) / 60),2) else 0 end as RunCalories
,RawActivity,RawSensorData

from (
select datetime(d.date,'+'||cast(pos as text)||' minute') as time
,case when b1 in (4,5) then 'Sleep' when b1 > 15 and b3 > 0 then 'Run' when b1 > 0 and b3 > 0 then 'Walk' else 'Idle' end as Description
,case when b1 not in (4,5) then b3 else 0 end as Steps
,b1 as RawActivity
,b2 as RawSensorData
,case when b3 > 0 then (b1 >> 4) else 0 end as Runs
--,case when b3 > 0 then (b1 & 0xf) else 0 end as mode
,case when b3> 0 and b1 not in (4,5) then case when b3 <= 90 then b3*h1*0.9 else b3*b3*h1/(case when b3 > 120 then 125 else 100 end) end else 0 end as iDistance

from blobdata b, date_data d,_Hght
where d.date = b.id
and b.b1 <> 126
order by b.id, pos
) a
, _PersonParams;

.output extract_raw_summary.csv

select time
,sum(Steps) as Steps
,sum(Runs) as Runs
,round(sum(iDistance)) as WalkDistance
,round(sum(case when runs>0 then (3+runs*2) * iDistance / 15 else 0 end)) as RunDistance
,round(sum(weight *2.2046 * iDistance * (select coalesce(max(Cal),0.95)/coalesce(max(Dist),40.233) from _CalList where Dist <= iDistance) / 60)) as WalkCalories
,round(sum(case when runs> 0 then (3+runs*2.0) /15 * weight *2.2046 * iDistance * (select coalesce(max(Cal),0.95)/coalesce(max(Dist),40.233) from _CalList where Dist <= iDistance) / 60 else 0 end)) as RunCalories

from
(select d.date as time
,case when b1 in (4,5) then 'Sleep' when b1 > 15 and b3 > 0 then 'Run' when b1 > 0 and b3 > 0 then 'Walk' else 'Idle' end as Description
,case when b1 not in (4,5) then b3 else 0 end as Steps
,b1 as RawActivity,b2 as RawSensorData
,case when b3 > 0 then (b1 >> 4) else 0 end as Runs
--,case when b3 > 0 then (b1 & 0xf) else 0 end as mode
,case when b3> 0 and b1 not in (4,5) then case when b3 <= 90 then b3*h1*0.9 else b3*b3*h1/(case when b3 > 120 then 125 else 100 end) end else 0 end as iDistance

from blobdata b, date_data d,_Hght
where d.date = b.id
and b.b1 <> 126
and b3 > 0
and (b1 & 0xf) <> 6
and (b1 & 0xf) <= 7
and (b1 & 0xf) > 0
and b1 not in (4,5)
) a, _PersonParams
group by time;

Can someone check the two files and give some feedback.

THX