I erg. Not competitively. I’m still kind of a noob.

It’s basically indoor rowing. You tend to cover fixed distances: 5000m, 2000m, 1000m. Sometimes 6km, 8km, 10km.

It’s like running, in that the shorter distances are faster: the pace is much quicker. At least, they should be.

All my paces could use a lot of work, to be honest, but it is nice to keep track of what pace I can actually row, especially recently. I really want to see my top 3 1000s, my top 3 2ks, my top 3 3ks, etc. That’s what I wrote the SQL to do.

One of the perks of erging in 2019 is that it’s all computerized. If you use the Concept2 app to record your workouts, and you Sync them to your online account, you can actually download an entire season as a database-file that you can import & analyze in SQL. If you have a heart rate monitor, that data gets logged and available to you as well.

Preliminaries

You’ll need a table already established SQL-side to import the data into. Here’s the create table you’re probably wanting:

create table erg2020 (
  "ID" numeric
  , "Date" timestamp with time zone
  , "Description" text
  , "Work Time (Formatted)" text
  , "Work Time (Seconds)" text
  , "Rest Time (Formatted)" text
  , "Rest Time (Seconds)" numeric
  , "Work Distance" numeric
  , "Rest Distance" numeric
  , "Stroke Rate/Cadence" numeric
  , "Stroke Count" numeric
  , "Pace" text
  , "Avg Watts" numeric
  , "Cal/Hour" numeric
  , "Avg Heart Rate" numeric
  , "Drag Factor" numeric
  , "Age" numeric
  , "Weight" text
  , "Type" text
  , "Ranked" text
  , "Comments" text
);

Importing

  1. Download the season’s CSV from log.concept2.com/history
  2. Unlike most CSVs, we’ll actually not try to clean it up before importing

  3. Run this regular SQL:

     copy erg2020 from '/path/to/Downloads/concept2-season-2020.csv' 
     with (format csv, null '', header, 
       force_null ("Rest Time (Seconds)", "Rest Distance", "Avg Heart Rate"));
    
  4. Done.

SQL

Now we can run some selects, do some analysis:

;with T as (
  select
    "Date"
    , "Work Distance"
    , row_number() over (
        partition by "Work Distance"
        order by "Work Time (Seconds)"
      ) as place
    , "Work Time (Seconds)"
    , "Pace"
    , "Avg Watts"
    , "Avg Heart Rate"
    , case when now() - "Date" < interval '3 weeks' then 'Recent' else '' end
    as "recent?"
  from erg2020
  where
    "Work Distance" % 500 = 0
    and "Work Distance" > 500
    and "Rest Time (Seconds)" is null
)
  select * from T
  where
    place <= 3
  order by
    "Work Distance"
    , place

That’s it.