Exporting data from Endomondo

**skatac** · 10-23-2012, 07:29 AM

I have been trying to find a way to export my workouts, split times, etc. from Endomondo for my own analysis and back-up purposes (I currently do not use any online features of Endomondo and keep my data on my Android phone).

I would like to know if and how others export their Endomondo data in some open format?

**skatac** · 10-23-2012, 08:01 AM

I'll start by sharing my first attempt to do a simple workout data export by using SQLite.

First, this is an overview of the steps:
1) Open Endomondo on your Android device and then Sign up with email "copydb" and password "endomondo" (this trick will make a copy of the Endomondo database in the root of your SD card)
2) Copy the EndomondoDatabase file from your SD card to your PC
3) Download and run SQLiteStudio (or some other SQLite client)
4) Add the Endomondo database in SQLiteStudio and open SQL query editor (Alt-E)
5) Copy the SQL statement from below, paste it into the query editor window and execute it
6) Export the result set into a CSV file (or some other file format of your choice)

EDIT: A few disclaimers:
- Tested on Endomondo PRO 8.2.0
- Tested with metric units only

The following SQLite statement will list all main workout records including a few calculated columns - avg_speed, avg_pace and total_pause:

Code:

SELECT  workoutId,
        sport,
        datetime(starttime/1000, 'unixepoch', 'localtime') as start_time,
        datetime(end_time/1000, 'unixepoch', 'localtime') as end_time,
        round(distance,2) as distance,
        time(duration, 'unixepoch') as duration,
        hrAvg as avg_heart_rate,
        calories,
        temperature,
        hydration,
        round(distance/duration*3600.0,2) as avg_speed,
        time(duration/distance, 'unixepoch') as avg_pace,
        time(max(0,(end_time-starttime)/1000-duration), 'unixepoch') as total_pause
FROM    workout w
WHERE   status>0

**skatac** · 10-23-2012, 08:18 AM

My second attempt is a query statement that will list all workouts with their split times and distances nested in.

EDIT: A few disclaimers:
- Tested on Endomondo PRO 8.2.0
- Tested with metric units only
- Tested with Running and Cycling sports
- Tested with Basic Workout and time-only and distance-only Interval Training programs
- Very slight time/distance differences may appear when compared to those that Endomondo reports
- The query execution time will depend on your computer's performance and Endomondo database size

Code:

-- first select main workout records:
SELECT  starttime || ':' || workoutId || ':A' as sort_key,
        sport,
        datetime(starttime/1000, 'unixepoch', 'localtime') as start_time,
        datetime(end_time/1000, 'unixepoch', 'localtime') as end_time,
        round(distance,2) as distance,
        time(duration, 'unixepoch') as duration,
        hrAvg as avg_heart_rate,
        calories,
        temperature,
        hydration,
        round(distance/duration*3600.0,2) as avg_speed,
        time(duration/distance, 'unixepoch') as avg_pace,
        time(max(0,(end_time-starttime)/1000-duration), 'unixepoch') as total_pause,
        0.0 as split_distance,
        time(0, 'unixepoch') as split_time,
        time(0, 'unixepoch') as subtotal_pause
FROM    workout w
WHERE   status>0
UNION ALL
-- for basic non-interval workouts, add lap times:
SELECT  w.starttime || ':' || w.workoutId || ':B' as sort_key,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        l.number as split_distance,
        time(l.splittime, 'unixepoch') as split_time,
        (select time(max(0,(t.timestamp-w.starttime)/1000-t.duration), 'unixepoch')
        from trackpoint t
        where t.workoutid=l.workoutid and
              t.timestamp=(select min(t2.timestamp)
                          from    trackpoint t2
                          where   t2.workoutid=l.workoutid and
                                  t2.distance>=l.number and
                                  t2.instruction=4
                         )
        ) as subtotal_pause
FROM    laptimes l, workout w
WHERE   l.type=1 AND w.status>0 AND
        w.workoutid=l.workoutid
UNION ALL
-- for interval workouts, add interval-program splits (time-only and distance-only programs are supported but no mixed programs)
SELECT  w.starttime || ':' || w.workoutId || ':B' as sort_key,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        round(max(t.distance- ((t.duration-i.split_duration) * t.speed/3600.0), i.split_distance), 2) as split_distance,
        time(max(i.split_duration, t.duration - ((t.distance-i.split_distance) / (t.speed/3600.0))), 'unixepoch') as split_time,
        time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause
FROM    (select i.programid, i.intervalid, i.duration, i.distance,
         sum(i2.duration) as split_duration, sum(i2.distance)/1000.0 as split_distance
         from interval i, interval i2
         where i2.programId=i.programId and i2.intervalId<=i.intervalId
         group by i.programid, i.intervalid, i.duration, i.distance
        ) i, 
        interval_program ip, workout w, trackpoint t
WHERE   w.status>0 AND
        i.programId=ip.programId AND
        ip.uuid=w.ipUuid AND
        t.workoutId=w.workoutId AND
        t.timestamp=max(
          (select min(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = w.workoutId and
                 t2.duration >= i.split_duration
          ),
          (select min(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = w.workoutId and
                 t2.distance >= i.split_distance
          )
        )
UNION ALL
-- add concluding split record to each workout:
SELECT  w.starttime || ':' || w.workoutId || ':C' as sort_key,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        round(t.distance, 2) as split_distance,
        time(t.duration, 'unixepoch') as split_time,
        time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause
FROM    workout w, trackpoint t
WHERE   w.status>0 AND
        t.workoutId=w.workoutId AND
        t.timestamp=
          (select max(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = w.workoutId and t2.instruction=4
          )
ORDER BY 1,14

**skatac** · 10-24-2012, 08:26 AM

I have managed to add support for Interval Training programs that combine time and distance intervals. The code below is a SQL script that should be copied and executed as a whole in the SQL query editor window.

A few disclaimers:
- Tested on Endomondo PRO 8.2.0
- Tested with metric units only
- Tested with Running and Cycling sports
- Tested with Basic Workout and Interval Training programs
- Very slight time/distance differences may appear when compared to those that Endomondo reports
- The query execution time will depend on your computer's performance and Endomondo database size

Code:

--List all workouts with their split times and distances nested in:
--first create three temp tables and a trigger that are needed to calculate split times for time/distance interval programs.
DROP TABLE IF EXISTS tmp_workout_interval;
DROP TABLE IF EXISTS tmp_interval_trackpoint;
DROP TABLE IF EXISTS tmp_interval_trackpoint_old;

CREATE TABLE tmp_workout_interval ( 
    programId  INTEGER,
    intervalId INTEGER,
    workoutId  INTEGER,
    intensity  INTEGER,
    duration   INTEGER,
    distance   INTEGER,
    PRIMARY KEY ( programId, intervalId, workoutId )
);
CREATE TABLE tmp_interval_trackpoint ( 
    workoutId  INTEGER,
    intervalId INTEGER,
    intensity  INTEGER,
    tpid       INTEGER,
    duration   REAL,
    distance   REAL,
    PRIMARY KEY ( workoutId, intervalId, tpid )
);
CREATE TABLE tmp_interval_trackpoint_old ( 
    workoutId  INTEGER,
    intervalId  INTEGER,
    duration   REAL,
    distance   REAL,
    PRIMARY KEY ( workoutId, intervalId)
);
CREATE TRIGGER tmp_workout_interval
       AFTER INSERT ON tmp_workout_interval
       FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO tmp_interval_trackpoint_old (workoutid, intervalid, duration, distance ) VALUES (NEW.workoutid, 0, 0,0)
    ;
    INSERT INTO tmp_interval_trackpoint_old (workoutid, intervalid, duration, distance )
    SELECT workoutid, intervalId, duration, distance
    FROM   tmp_interval_trackpoint
    WHERE  workoutId=NEW.workoutId AND
           intervalId=NEW.intervalId-1
    ;
    INSERT INTO tmp_interval_trackpoint (workoutid, intervalid, intensity, tpid, duration, distance )
    SELECT
       NEW.workoutId,
       NEW.intervalId,
       NEW.intensity,
       t.tpid,
       -- here is a bit of mathematics whereby either interval duration or distance must be 0, hence the MAX function.
       -- the second argument in the MAX function also handles a possible residuum by knocking it off.
       max(ito.duration + NEW.duration, t.duration - ((t.distance-ito.distance-NEW.distance/1000.0) / (t.speed/3600.0))),
       max(ito.distance + NEW.distance/1000.0, t.distance- ((t.duration-ito.duration-NEW.duration) * t.speed/3600.0))
     FROM trackpoint t, tmp_interval_trackpoint_old ito
     WHERE
       t.workoutId=NEW.workoutId AND
       ito.workoutId=NEW.workoutID AND
       ito.intervalId=NEW.intervalID-1 AND
       t.timestamp=max(
          (select min(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = NEW.workoutId and
                 t2.speed > 0 and
                 t2.duration >= ito.duration + NEW.duration
          ),
          (select min(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = NEW.workoutId and
                 t2.speed > 0 and          
                 t2.distance >= ito.distance + NEW.distance/1000.0
          )
        )
     ;
END
;
--The following Insert will engage the trigger for the temporary split time calculation:
INSERT INTO tmp_workout_interval
SELECT i.programId,
       i.intervalId,
       w.workoutId,
       i.intensity,
       i.duration,
       i.distance
FROM interval i, interval_program ip, workout w
WHERE   w.status>0 AND
        i.programId=ip.programId AND
        ip.uuid=w.ipUuid
ORDER BY w.workoutId, i.intervalId
;

-- And finally the query made up of four record groups (A,B,C,X):

-- first select main workout records:
SELECT  workoutId,
        'A (workout)' as grp_sort,
        datetime(starttime/1000, 'unixepoch', 'localtime') as start_time,
        datetime(end_time/1000, 'unixepoch', 'localtime') as end_time,
        sport,
        round(distance,2) as distance,
        time(duration, 'unixepoch') as duration,
        hrAvg as avg_heart_rate,
        calories,
        temperature,
        hydration,
        round(distance/duration*3600.0,2) as avg_speed,
        time(duration/distance, 'unixepoch') as avg_pace,
        time(max(0,(end_time-starttime)/1000-duration), 'unixepoch') as total_pause,
        0.0 as split_distance,
        time(0, 'unixepoch') as split_time,
        time(0, 'unixepoch') as subtotal_pause,
        '' as intensity
FROM    workout w
WHERE   status>0
UNION ALL
-- for basic non-interval workouts, add lap times:
SELECT  w.workoutId,
        'B (lap)' as grp_sort,
        datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        l.number as split_distance,
        time(l.splittime, 'unixepoch') as split_time,
        (select time(max(0,(t.timestamp-w.starttime)/1000-t.duration), 'unixepoch')
        from trackpoint t
        where t.workoutid=l.workoutid and
              t.timestamp=(select min(t2.timestamp)
                          from    trackpoint t2
                          where   t2.workoutid=l.workoutid and
                                  t2.distance>=l.number and
                                  t2.instruction=4
                         )
        ) as subtotal_pause,
        '' as intensity
FROM    laptimes l, workout w
WHERE   l.type=1 AND w.status>0 AND
        w.workoutid=l.workoutid
UNION ALL
-- for interval workouts, add interval-program splits (time-only and distance-only programs are supported but no mixed programs)
SELECT  w.workoutId,
        'C (interval)' as grp_sort,
        datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        round(it.distance, 2) as split_distance,
        time(it.duration, 'unixepoch') as split_time,
        time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause,
        it.intensity
FROM    tmp_interval_trackpoint it, trackpoint t, workout w
WHERE   it.workoutId=w.workoutId AND
        it.tpid=t.tpid
UNION ALL
-- add a concluding split record to each workout:
SELECT  w.workoutId,
        'X (last split)' as grp_sort,
        datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        round(t.distance, 2) as split_distance,
        time(t.duration, 'unixepoch') as split_time,
        time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause,
        '' as intensity
FROM    workout w, trackpoint t
WHERE   w.status>0 AND
        t.workoutId=w.workoutId AND
        t.timestamp=
          (select max(t2.timestamp)
          from   trackpoint t2
          where  t2.workoutId = w.workoutId and t2.instruction=4
          )
ORDER BY 3,1,2,15
;

**chriskrycho** · 10-27-2012, 09:06 PM

This is extremely helpful info. Question, though: how did you log in as copydb/endomondo? Or rather: how did you log out? If it's the normal log out, this (alas!) won't work for me—no fault of yours—because it (says it) deletes the database and re-downloads from the site, and I need to tackle it because it's not recording a run correctly: the phone has it, but the site got it wrong.

Any insight would be great; I'm perfectly comfortable tackling the SQLite site of things once I've gotten the backup, but getting there is the problem.

**skatac** · 10-28-2012, 11:28 AM

I don't use the website so every time I start Endomondo, it opens the sign-up screen. There I just select "I have an account" and then "Log in with email". Once I put copydb/endomondo in, it shows a "Done" message.

I'm not sure why you can't log out without overwriting your local database. Have you tried searching the Endomondo forums for some tips?

**chriskrycho** · 10-29-2012, 04:17 PM

I'm not positive you can't, actually; I'm going off the message they throw at you when you go to "log out" of the profile on the phone:

This will erase your history on the phone and cannot be undone. If your workouts have been uploaded to the web, they are still accessible on the website.

I've looked all through the getsatisfaction pages, and not much there, alas. It's not the end of the world, obviously; just a faint hope I'll be able to recover some data. Thanks.

EDIT: Yep, that went well. As in: they just delete the database contents entirely if you log out and you connect your profile to the website. As I expected it would, given their statement to that effect. But it's quite annoying, as there's apparently no way to get to the data otherwise. *sigh*

**skatac** · 10-30-2012, 01:19 AM

Sorry to hear that, I hope you didn't lose too much data. I was just wondering what would have happened if you had disabled your Internet connection before doing the switch.

If you have any questions or comments on the SQL side of things, let me know.