Data management¶

The "Data Management" screen (available from the side menu) provides overall user data import, export, delete, view functions, allowing complete ownership of all data Gadgetbridge created or collected.

The Gadgetbridge activity database is exported as an SQLite file called Gadgetbridge (without an file extension) and can be used for further analysis or visualization.

Data folder location¶

When exporting/importing data from/to Gadgetbridge, a dedicated folder called files is used.

The exact location of this folder might depend on the phone and Android version and therefore the export/import location is listed in the "Data Management" screen.

Very often, the folder path is:

/storage/emulated/0/Android/data/nodomain.freeyourgadget.gadgetbridge/files/

Do note that if you use the Bangle.js or Nightly or any other product flavor of Gadgetbridge, the path will be slightly different, based on the name of that variant.

Android 12 and lowerAndroid 13

This folder can be normally accessed via some file manager. Under Android 11 and newer versions, the data folder can only be accessed via file manager that is using the new Scoped Storage system of Android.

Here are some FLOSS file managers that can do this:

• "Simple File Manager Pro", on F-Droid and on Google Play
• "Material Files", on F-Droid and on Google Play

Accessing Android/data folder on external file managers are no longer possible without root.

However, AOSP Files app allows you to read the contents of Android/data. If AOSP Files app is hidden on your Android device, you can install a shortcut app that opens Files. Make sure to show hidden/protected files.

It is also still possible to access the folder with adb, however it requires a PC.

Some Android versions might not have yet created the /storage/emulated/0, resulting in Gadgetbridge error saying, 'Cannot find export path'. This was probably because there was no /storage/sdcard0/ directory. After inserting external storage and tried exporting, Android probably created the /storage/sdcard0/ directory. /storage/sdcard0/ is still in local storage.

Exporting your data¶

You can manually or automatically export the data to get copy of your data.

The exported database contains your activity, sports activities and other data, like preference settings. Activity data is exported as an sqlite database. Sports activities (if captured as GPX) are exported as GPX files. Preference data are also exported in the form of XML files. All these files are exported into the data folder and inside the zip file.

Manual export¶

While in "Data management" page (can be accessed from side menu while in home screen), click on "Export zip". Then, select where to save the file. A zip file will be created in the selected location, which will contain a full backup of all data present in Gadgetbridge.

Automatic export¶

Gadgetbridge activities database can be automatically exported periodically for external backups, sync, processing etc. To enable auto-export, go to "Settings → Auto export → Auto export enabled → Yes".

This export only includes the activities database, and is not a full export of Gadgetbridge. Data such as preferences and gpx files will not be included.

Then, select the export directory and name of the exported file via "Export location", and set interval period (in hours) via "Export interval".

To test auto-export if it is working, you can trigger an auto-export at any time via, "Database management → AutoExport → Run AutoExport now".

Importing data¶

File previously exported via manual export can be re-imported (current data will be overwritten!) by simply clicking "Import zip" and selecting a previous exported file. After the file is imported, Gadgetbridge will restart automatically.

Try to make sure that when you import the data, ideally you use the same version of Gadgetbridge that was used for exporting. If you are restoring some old backup, try to get older version of Gadgetbridge and then update Gadgetbridge. This will allow the internal update process to take care of updating internal data structures (running database and preference migrations).

Reading the database¶

The sqlite database file Gadgetbridge can be open on desktop by using various tools, like DBeaver and others.

While it is generally better to export the database and open it on desktop, if you want to view and manage on your Android device you can use SQLiteViewer. Do note that exporting the data will take a long time. Which can take about 10 minutes per one year of data.

SQL examples¶

Extracting CSV data¶

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: StackOverflow

Calculate time between Heart Rate samples¶

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 average daily steps¶

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)

Steps per day¶

select date(TIMESTAMP, 'unixepoch') as "Date", sum(STEPS) as "Steps"
from PEBBLE_HEALTH_ACTIVITY_SAMPLE
group by date(TIMESTAMP, 'unixepoch')

Sleep per day¶

select
  round(sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600.0,1) as "Duration",
  sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600 as "Hours",
  sum(TIMESTAMP_TO-TIMESTAMP_FROM)%3600/60 as "Minutes",
  datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start",
  datetime(max(TIMESTAMP_TO), 'unixepoch') as "End"
from PEBBLE_HEALTH_ACTIVITY_OVERLAY
where RAW_KIND = 1
group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day');

Sleep, deep sleep, nap and deep nap per day¶

select
  round(sum(case when RAW_KIND = 1 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Sleep Duration",
  round(sum(case when RAW_KIND = 2 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Sleep Duration",
  round(sum(case when RAW_KIND = 3 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Nap Duration",
  round(sum(case when RAW_KIND = 4 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Nap Duration",
  datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start",
  datetime(max(TIMESTAMP_TO), 'unixepoch') as "End"
from PEBBLE_HEALTH_ACTIVITY_OVERLAY
where RAW_KIND in (1, 2, 3, 4)
group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day');

Sleep per hour¶

select
    count(timestamp), strftime('%Y-%m-%d %H:%M', datetime(max(timestamp),
    'unixepoch', 'localtime')) from MI_BAND_ACTIVITY_SAMPLE where RAW_KIND=112
    group by strftime('%Y%m%d%H',datetime(timestamp, 'unixepoch'));

Migrate from another gadget¶

You can merge your activity data exported from a previous gadget with the new one.

If your both new and previous gadget is the same brand and model, and if the old gadget is still listed on Gadgetbridge, you can just tell Gadgetbridge to change old MAC address to new one with sending an intent.

This applies to cases where you factory reset your gadget (because factory resetting also changes MAC address for most gadgets, so it appears as a brand-new gadget to Gadgetbridge) or you just bought another one because old one is no longer accessible (broken, bricked, lost etc.). If so, follow the link below.

Change gadget MAC address

But, if both new and previous gadgets are different models, continue reading below to learn how to manually merge your data from a previous Gadgetbridge export to currently existing one.

Make fresh new export as per Export data and open it in sqlite3, presuming that the file name is Gadgetbridge:

sqlite3 Gadgetbridge

Open older file with previous data in this sqlite3 instance, presuming that the previous data file name is Gadgetbridge_old:

ATTACH 'Gadgetbridge_old' as old;

Insert old activity data (steps, sleep) into fresh backup:

insert into MI_BAND_ACTIVITY_SAMPLE SELECT * from old.MI_BAND_ACTIVITY_SAMPLE;

You can do the same with recorded workouts data:

insert into BASE_ACTIVITY_SUMMARY SELECT * from old.BASE_ACTIVITY_SUMMARY;

Device ID¶

Devices inside Gadgetbridge database have a unique device ID so if you want to "merge" the data into the same new device, you need to change the device ID for all the data to the ID of the "new" device.

This can be done after you inserted old data into new database as indicated above.

select * from device;

Check which device is the new one (by looking at their HW address or by the alias). IDs are just numbers, like 1, 2, 3... Note the "new" device ID.

Now change the device ID for all the rows in the database, for example if the new device ID is a number 2:

update MI_BAND_ACTIVITY_SAMPLE set device_id=2;
update BASE_ACTIVITY_SUMMARY set device_id=2;

If you have multiple devices, you will probably only want to update record of the old device... you can therefore limit the update in this way (where 1 was the old device):

update BASE_ACTIVITY_SUMMARY set device_id=2 where device_id=1;

Base activity ID¶

Base activities (workouts) have a sequential ID in the Gadgetbridge database so if you are to merge some old data and new data, you need to make sure that you correctly increment the ID as well, otherwise you will get a duplicate ID error.

This you need to do on the new data before you can insert old data into new database:

select max(_id) from old.BASE_ACTIVITY_SUMMARY;

This gives you the ID of the last activity, lets call it xxx,

Now you add (as in addition) this value to all the current ids in the base activity summary:

update BASE_ACTIVITY_SUMMARY set _id = _id + xxx;

Now you can insert the old records into the new database, without errors:

insert into BASE_ACTIVITY_SUMMARY SELECT * from old.BASE_ACTIVITY_SUMMARY;

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.

Python examples¶

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()

Import data from Mi Fit¶

• Get your Mi Fit data via GDPR data request, either from Mi Fit or via this Huami privacy settings page.

! Fix for Huami privacy page
user.huami.com##:matches-path(/privacy2/index.html) .gdpr-container:watch-attr(style):style(display: block !important;)

• Make an export of database in Gadgetbridge (make an extra backup of this exported database).
• Unzip received Mi Fit data and place the .csv files into a single folder (see list of required files below).
• Put exported Gadgetbridge database file into the same folder.
• Put this script into the same folder.
• Either remove "numbers" from the .csv file names, or rename the xxx_file_name variables below.
• You may need to edit device_id and user_id. for most people (with one device) this will remain as is below.
• Run this script with python3: python import_from_mifit.py.
• Re-import the updated Gadgetbridge database file to GB.

#!/usr/bin/env python3

import csv
import datetime
import sys
import sqlite3
import random

# import script to get MiFit data into Gadgetbridge database

# what this tool does:
# - it checks if a particular record (based on timestamp) is in database
# - if record does not exist, it is created:
# - steps are added
# - for sleep, separate minute based records are created
# - all records will have heart rate measurements, if available in the data

# what this tool does not:
# - doesn't import activities

# it can damage your data, make plenty of backups to be able to roll back at any point

# 1) get your MiFit data via GDPR data request, instructions:
# http://gadgetbridge.org/internals/development/data-management/#import-data-from-mi-fit

# 2) make an export of database in Gadgetbridge
# - make an extra backup of this exported database
# 3) unzip received MiFit data and place the .csv files into a single folder (see list of required files below)
# 4) put exported Gadgetbridge database file into the same folder
# 5) put this script into the same folder
# 6) either remove "numbers" from the .csv file names, or rename the xxx_file_name variables below
# 7) you may need to edit device_id and user_id. for most people (with one device) this will remain as is below
# 8) run this script with python3:
#  python import_from_mifit.py
# 9) re-import the updated Gadgetbridge database file to GB

activity_file_name = "ACTIVITY_MINUTE.csv"
hr_file_name1 = "HEARTRATE.csv"
hr_file_name2 = "HEARTRATE_AUTO.csv"
sleep_file_name = "SLEEP.csv"

database = "Gadgetbridge"
device_id = 1
user_id = 1

# do not edit below

conn = sqlite3.connect(database)
cursor = conn.cursor()

# build HR dictionary
hr = {}

data = csv.reader(open(hr_file_name1), delimiter=",")
# 1572088219,81
next(data)  # skip header
for line in data:
    hr[line[0]] = line[1]

data = csv.reader(open(hr_file_name2), delimiter=",")
# 2017-07-14,23:35,54
next(data)  # skip header
for line in data:
    date = "{0},{1}".format(*line)
    dt = datetime.datetime.strptime(date, "%Y-%m-%d,%H:%M")
    # timestamp=dt.timestamp()
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()
    hr[timestamp] = line[2]

# steps
data = csv.reader(open(activity_file_name), delimiter=",")
# 2017-07-04,13:18,11
next(data)  # skip header
for line in data:
    # print(line)
    date = "{0},{1}".format(*line)
    dt = datetime.datetime.strptime(date, "%Y-%m-%d,%H:%M")
    w = {}
    # timestamp=dt.timestamp()
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()
    w["timestamp"] = timestamp
    r = cursor.execute(
        "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
    ).fetchone()
    if r:
        # print("record exists", r,line[2])
        pass
    else:
        steps = int(line[2])
        heart_rate = hr.get(timestamp, 255)
        raw_intensity = random.randint(10, 130)
        if steps < 80:
            raw_kind = 1  # slow walking
        elif 100 > steps > 80:
            raw_kind = 1  # 3 fast walking, unsupported by GB
        else:
            raw_kind = 4  # 4 running
        print("inserting", steps, heart_rate)
        cursor.execute(
            "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
            (timestamp, device_id, user_id, raw_intensity, steps, raw_kind, heart_rate),
        )

# sleep
data = csv.reader(open(sleep_file_name), delimiter=",")
# 2017-07-05,45,348,0,2017-07-05 08:23:00+0000,2017-07-05 08:23:00+0000
next(data)  # skip header
for line in data:
    deep_sleep = int(line[1])
    light_sleep = int(line[2])

    dt = datetime.datetime.strptime(line[4], "%Y-%m-%d %H:%M:%S%z")
    timestamp = dt.replace(tzinfo=datetime.timezone.utc).timestamp()

    dt_to = datetime.datetime.strptime(line[5], "%Y-%m-%d %H:%M:%S%z")
    ts_to = dt_to.replace(tzinfo=datetime.timezone.utc).timestamp()

    # deep sleep
    # timestamp=ts_from
    for i in range(0, deep_sleep):

        w["timestamp"] = timestamp
        r = cursor.execute(
            "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
        ).fetchone()
        if r:
            # print("record exists", r,line[2])
            pass
        else:
            heart_rate = hr.get(timestamp, 255)
            print("inserting sleep", timestamp)
            steps = 0
            raw_kind = 123
            raw_intensity = random.choice([20, 2, 7] + [0] * 20)
            cursor.execute(
                "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
                (
                    timestamp,
                    device_id,
                    user_id,
                    raw_intensity,
                    steps,
                    raw_kind,
                    heart_rate,
                ),
            )

        timestamp = timestamp + 60

    for i in range(0, light_sleep):

        w["timestamp"] = timestamp
        r = cursor.execute(
            "SELECT * from MI_BAND_ACTIVITY_SAMPLE where TIMESTAMP=$timestamp", (w)
        ).fetchone()
        if r:
            # print("record exists", r,line[2])
            pass
        else:
            heart_rate = hr.get(timestamp, 255)
            print("inserting sleep", timestamp)
            steps = 0
            raw_kind = 121
            raw_intensity = random.choice([20, 2, 7] + [0] * 20)
            cursor.execute(
                "INSERT INTO MI_BAND_ACTIVITY_SAMPLE VALUES (?,?,?,?,?,?,?)",
                (
                    timestamp,
                    device_id,
                    user_id,
                    raw_intensity,
                    steps,
                    raw_kind,
                    heart_rate,
                ),
            )

        timestamp = timestamp + 60

conn.commit()
conn.close()

Understanding of the activity data¶

Most of the "understanding" of the activity data are actually assumptions. There are several related issues in the tracker with long discussions, see them typically under the research label.

Here's some references for Mi Band data analysis:

• https://rstudio-pubs-static.s3.amazonaws.com/178074_8a8f987bb4c047fdb13cbafc81cca58d.html - (archived)
• https://github.com/ckesc/miband_analytics

View data in external applications¶

• GadgetStats is a companion app for Gadgetbridge, developed with Ionic.
• miband2_analysis
• PyFit is a non-functional (with no way to contact the author to report bugs) open-source fitness tracker prototype written in Python with a GTK interface, currently working on Linux on the GNOME desktop environment. The data can be either inputted by the user or imported from a database generated by the Gadgetbridge gadget companion app for Android (only Mi Bands are working so far).

Integrate with self-hostable fitness tracking service¶

See the discussion here issue #49 for list of interesting fitness tracking services.

Similar projects¶

• Amazfish