Data Export, Import, Merging, Processing…

Make sure to always keep backups of your data. Commands below could be descructive. You can use the Data export or Data Auto export to get copy of your data.

Contents

• Data export
• Data import
• Data Auto export
• Extracting CSV Data from the SQLite Database
• Calculate time between Heart Rate samples, in SQL
• Merge data from old Gadgetbridge export
• Calculate daily steps average, in SQL
• Draw charts for year/month/week/day in Python
• View data in external applications

Data export

The Gadgetbridge activity database can be exported to SQLite format to extract its data for further analysis.

Under the main menu, go to Database management. Exported data will be in the Gadgdetbridge file under a directory shown on the screen.

For this example we’ll assume the file to be /storage/emulated/0/Android/data/nodomain.freeyourgadget/files/Gadgdetbridge.

Data import

File previously exported via export function can be re-imported back (current data will be overwritten!) by placing the database file into /storage/emulated/0/Android/data/nodomain.freeyourgadget/files/Gadgdetbridge.

Data Auto Export

Gadgetbridge data cab be automatically exported periodically for external backups, sync, processing etc. To set Autoexport, go to Settings → Auto export → Auto export enabled → Yes.

Selected export directory and name of the exported file via Export location

Set interval period (in hours) via Export interval.

Extracting CSV Data from the SQLite Database

This shows how to access the previously exported database on Debian GNU/Linux 9, the sqlite3 and android-tools-adb packages are required.

To list all tables in the database:

$ adb shell
OnePlus3:/ $ su
OnePlus3:/ # cd /storage/emulated/0/Android/data/nodomain.freeyourgadget.gadgetbridge/files
OnePlus3:/ # sqlite3 Gadgetbridge
SQLite version 3.19.4 2017-08-18 19:28:12
Enter ".help" for usage hints.
sqlite> .table

ACTIVITY_DESCRIPTION            NOTIFICATION_FILTER           
ACTIVITY_DESC_TAG_LINK          NOTIFICATION_FILTER_ENTRY     
ALARM                           PEBBLE_HEALTH_ACTIVITY_OVERLAY
BASE_ACTIVITY_SUMMARY           PEBBLE_HEALTH_ACTIVITY_SAMPLE 
CALENDAR_SYNC_STATE             PEBBLE_MISFIT_SAMPLE          
DEVICE                          PEBBLE_MORPHEUZ_SAMPLE        
DEVICE_ATTRIBUTES               TAG                           
HPLUS_HEALTH_ACTIVITY_OVERLAY   USER                          
HPLUS_HEALTH_ACTIVITY_SAMPLE    USER_ATTRIBUTES               
ID115_ACTIVITY_SAMPLE           XWATCH_ACTIVITY_SAMPLE        
MI_BAND_ACTIVITY_SAMPLE         ZE_TIME_ACTIVITY_SAMPLE       
NO1_F1_ACTIVITY_SAMPLE          android_metadata 

To export to CSV:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output out.csv
sqlite> select * from BASE_ACTIVITY_SUMMARY;

Reference: SO.

Calculate time between Heart Rate samples, in SQL

SELECT "TIMESTAMP",datetime("TIMESTAMP",'unixepoch','localtime') as DATETIME, STEPS, HEART_RATE, ("TIMESTAMP"- LAG("TIMESTAMP") OVER())/60 as TIME_DIFF
FROM MI_BAND_ACTIVITY_SAMPLE
WHERE "TIMESTAMP" BETWEEN (strftime('%s','2019-08-02 16:15:00','utc')) and (strftime('%s','2019-08-03 23:15:00','utc'))
and HEART_RATE<>255

Calculate daily steps average, in SQL

select avg(a) from (select strftime('%Y.%m.%d', datetime(timestamp, 'unixepoch')) as d,sum(STEPS)as a from MI_BAND_ACTIVITY_SAMPLE group by d)

Merge data from old Gadgetbridge export

You can merge data from previous export (for example another Xiaomi device) into new one by opening your current export and inserting all data from an older file:

Make fresh new export as per Data export and open it in sqlite3:

sqlite3 Gadgetbridge

Open older file with previous data in this sqlite3 instance:

ATTACH 'Gadgetbridge_old' as old;

Insert old data into fresh backup:

insert into MI_BAND_ACTIVITY_SAMPLE SELECT * from old.MI_BAND_ACTIVITY_SAMPLE;

Close sqlite3, make sure to put this fresh Gadgetbridge file back into /storage/emulated/0/Android/data/nodomain.freeyourgadget.gadgetbridge/files/ and perform Data import.

Draw charts for year/month/week/day in Python

import sqlite3
import matplotlib.pyplot as plt
import datetime
import numpy as np

conn = sqlite3.connect('Gadgetbridge')
c = conn.cursor()
min_steps_per_minute=00

d=c.execute("select strftime('%Y.%m.%d', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
w=c.execute("select strftime('%Y.%W', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
m=c.execute("select strftime('%Y.%m', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
y=c.execute("select strftime('%Y', datetime(timestamp, 'unixepoch')) as d,sum(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? group by d",(min_steps_per_minute,)).fetchall()
print("all avg:",c.execute("select avg(STEPS) from MI_BAND_ACTIVITY_SAMPLE where STEPS > ? ",(min_steps_per_minute,)).fetchall())

db={x[0]:x[1] for x in d}
wb={x[0]:x[1] for x in w}
mb={x[0]:x[1] for x in m}
yb={x[0]:x[1] for x in y}

fig, ax = plt.subplots(4)

def doit(where,what,color,label):
    where.bar(
        np.arange(len(what)),
        list(what.values()),
        0.3,
        #tick_label=list(what.values()),
        tick_label=list(what.keys()),
        label=label,
        color=color,
    )
    where.legend()
    #where.xticks(rotation=60)

doit(ax[3],yb,"g","steps/year")
doit(ax[2],mb,"b","steps/month")
doit(ax[1],db,"r","steps/day")
doit(ax[0],wb,"g","steps/week")

for ax in fig.axes:
    plt.sca(ax)
    plt.xticks(rotation=65)
plt.show()
c.close()

View data in external applications

• GadgetStats is a Companion app for Gadgetbridge, developed with Ionic.