Gadgetbridge: Database import from MI fit

[geripgeri]

Hi,

I have a full backup of my MI fit database, also a nice csv file.
As I see, the database structure is not completely compatible. Is there any solution for importing existing data?

Thanks!

[danielegobbetti]

Difficult to tell unless you share the schema of the data you have.

[geripgeri]

I'm used the MiFit_1.3.912_401_en_notify version (http://en.miui.com/thread-53761-1-1.htm)

I just simply coped the database file from /data/data... After that I created a csv file form it using this: http://forum.xda-developers.com/general/accessories/xiaomi-mi-band-data-extraction-t3019156

BTW As I see the Gadgetbridge stores all activity vs. Mi fit stores old activities aggregated by day

Database example:
Cols:

| Name | Type |
| --- | --- |
| id | INTEGER PRIMARY KEY AUTOINCREMENT |
| type | INTEGER |
| source | INTEGER |
| date | TEXT |
| summary | TEXT |
| indexs | TEXT |
| data | BLOB |
| sync | INTEGER |

Data:

| id | type | source | date | summary | indexs | data | sync |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 239 | 0 | 0 | 2016-03-05 | {v:5,slp:{st:1457124180,ed:1457154000,dp:196,lt:300,wk:1},stp:{ttl:12666,dis:8412,cal:373,wk:156,rn:9,runDist:902,runCal:39},goal:10000} | [{startIndex:0,stopIndex:5756}] | BLOB | 1 |

CSV example:

Date,InBedMin,DeepSleepMin,LightSleepMin,SleepStart,SleepEnd,AwakeMin,DailyDistanceMeter,DailySteps,DailyBurnCalories,WalkDistance,WalkTimeMin,WalkBurnCalories,RunDistanceMeter,RunTimeMin,RunBurnCalories,WalkRunSeconds,DateUS,Activity,BedHour,BedMinute,AwakeHour,AwakeMinute,DateUnix,SleepStartUnix,SleepEndUnix,WalkStart,WalkEnd,RunkEnd

"2016-03-05 ",496,196,300,"2016-03-04 21:43:00","2016-03-05 06:00:00",1,8412,12666,373,7510,156,334,902,9,39,9900,"Mar 05, 2016","Daily Activity",21,43,06,00,1457132400,1457124180,1457124180,1457161200,1457170560,1457171100

[danielegobbetti]

ot would be interesting to store the historical data in the same way. Perhaps even giving the user some way to choose after how many days the data should be moved to this archive table.

some fields are trivial (sum of steps etc), for some we have formulas which are easy (estimate distance from steps), other are to research though (telling apart walk from run, etc)...

There's definitely lots of work before we can support this fully (not the import, but computing these values ourselves).

As there is no device communication involved, this task would be ideal for someone who wants to help us with some code :) Anyone up to the task? Thanks!

[ironjan]

I wanted to switch to Gadgetbridge [Gb] for some time now - but discovered Gb too late (related to #480) and already had too much data in mi fit. With the now possible web export, I could take a look at the data import. However, this won't happen before Christmas and I'd need some help to "sort" the export data into the correct Gb's tables.

Export URL: https://user.huami.com/hm_account/2.0.0/index.html?loginPlatform=web&platform_app=com.xiaomi.hm.health&v=s3.1.1#/

I checked the files and the numbers matched the Mi Fit app so far. The export contained 6 CSV files in my case.

ACTIVITY_<ID>.csv
date,lastSyncTime,steps,distance,runDistance,calories

BODY_<ID>.csv
timestamp,weight,height,bmi,fatRate,bodyWaterRate,boneMass,metabolism,muscleRate,visceralFat,impedance

HEARTRATE_<ID>.csv
date,lastSyncTime,heartRate,timestamp

SLEEP_<ID>.csv
date,lastSyncTime,deepSleepTime,shallowSleepTime,wakeTime,start,stop

SPORT_<ID>.csv
type,startTime,sportTime,distance,maxPace,minPace,avgPace,calories

USER_<ID>.csv
userId,gender,height,weight,nickName,avatar,birthday

[amarbme13]

Even i have the same export as ironjan said. I'm not so clear with the data that comes in. Is there any kind of metadata associated with this export? I have mailed the xiaomi team about this. I will get back to you once they reply!

If you know of the metadata please comment!

[matteo0026]

It would be nice if someone can get working database import from MiFit. I would migrate everything to GadgetBridge, but I have at least 3 years of data from MiFit (I have a MiBand 2)

[micw]

Hello,
I spend a few hours in reverse-engineering the API that mi fit uses. It can be used to retrieve step and sleep data from mi cloud. Pulse and weight data is probably also there.

Here are my findings include a (working) python PoC: https://github.com/micw/hacking-mifit-api

[vanous]

@micw i can never log in, as i never receive the access token, any idea why?

{'error': ['401'], 'state': ['REDIRECTION'], 'region': ['cn-north-1']}
Error: No access token in response

Btw, Huami switched to provide export also in minute resolution in GDPR export, based on some user's push back:

ACTIVITY_MINUTE_xxx.csv:

date,time,steps
2019-01-20,23:02,14
2019-01-21,06:05,61
2019-01-21,06:06,38
2019-02-16,21:54,10

[micw]

@vanous There are several login options, the one I use (and have implemented) is email sign in (the very last option in the list). Which one do you use?

[vanous]

Well... i had thought i use the email option but after browsing around via the web interface today, i actually think they have made me (unknowingly) to create MI account...

[micw]

If I find some time this evening, I'll try to implement this. It's unfortunately a bit more complicated than the email login.

Edit: evening GMT+2 ;)

[vanous]

thank you!

[micw]

I found a little time to check it. Unfortunately, the log in shows a captcha in most cases. So it's really hard to automate it ;-(

[vanous]

so what if we log in via web, is there a way to see the access token, which could then be used in the script?

[micw]

This is possible but not through the "normal" login. You need to start at

https://account.xiaomi.com/oauth2/authorize?skip_confirm=false&client_id=428135909242707968&pt=1&scope=1+6000+16001+20000&redirect_uri=https%3A%2F%2Fapi-mifit-cn.huami.com%2Fhuami.health.loginview.do&_locale=de_DE&response_type=code

and capture all the traffic (developer tools) [edit: no need to capture the traffic]. You end up at the page:

https://api-mifit-cn.huami.com/huami.health.loginview.do?code=...

Then you need to call:
mifit_login_with_token({
'grant_type': 'access_token',
'country_code': country_code,
'code': access_token,
})

"access_token" is the code from that URL.

This returns a response containing the huami user id and an access token. These both can be used for any further communication with the API. I assume that the app token not expire because it's the one used by the original app after i terminate and re-start it.

To be sure, I terminated the app and retry in >24h to see what communication it does in this case.

[vanous]

@micw this is great, thank you. unfortunately, i still get {'error_code': '0106'} ... what is a correct country code?

i have added code based login, can send you PR too...
edit: done

[vanous]

OK, i have imported my old data from MiFit, via this script: https://codeberg.org/Freeyourgadget/Gadgetbridge/wiki/Data-Export-Import-Merging-Processing#import-data-from-mifit

I got the data via GDPR request, rather then via the API access as i think this can be more accessible to everyone.

See disclaimers in the wiki text, backup your data and after the import, check if you like what you see. Feel welcome to improve upon this.