Results 1 to 9 of 9
  1. Thread Author  Thread Author    #1  

    Lightbulb Exporting data from Endomondo

    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?
  2. Thread Author  Thread Author    #2  

    Default Re: Exporting data from Endomondo

    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
    Last edited by skatac; 10-23-2012 at 08:17 PM.
  3. Thread Author  Thread Author    #3  

    Default Re: Exporting data from Endomondo

    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
    Last edited by skatac; 10-23-2012 at 08:31 PM.
  4. Thread Author  Thread Author    #4  

    Default Re: Exporting data from Endomondo

    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
    ;
  5. #5  

    Default Re: Exporting data from Endomondo

    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.
  6. Thread Author  Thread Author    #6  

    Default Re: Exporting data from Endomondo

    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?
  7. #7  

    Default Re: Exporting data from Endomondo

    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*
    Last edited by chriskrycho; 10-29-2012 at 04:32 PM. Reason: more info
  8. Thread Author  Thread Author    #8  

    Default Re: Exporting data from Endomondo

    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.
  9. #9  

    Question Re: Exporting data from Endomondo

    In Version 10 is no option to logout without deleting all local data. So step one doesn't work. Is there any other way to grap the sqlite?

    Marcus

Similar Threads

  1. Move apps and data from Incredible to Droid X
    By ath2o in forum Motorola Droid X
    Replies: 4
    Last Post: 08-04-2010, 10:39 AM
  2. Transfer apps/data from Droid to Incredible
    By joeturni in forum Verizon Droid Incredible
    Replies: 3
    Last Post: 04-28-2010, 11:26 AM
  3. How to remove Google data from phone
    By mmhhc in forum General Help and How To
    Replies: 3
    Last Post: 03-05-2010, 01:17 AM
  4. transfering data from palm to android
    By CaptainET in forum Other OS's and Devices
    Replies: 0
    Last Post: 02-24-2010, 04:56 PM
  5. transfer data from Treo 755p to Samsung Moment
    By gadget in forum Samsung Moment
    Replies: 5
    Last Post: 11-21-2009, 08:14 PM

Posting Permissions

B