Import RubiTrack Summary CSV on Database PostgreSQL

Rubitrack : https://www.rubitrack.com/ .
The command done :

# psql -U user -d rubitrack
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1), server 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
Type "help" for help.

rubitrack=> CREATE table rubitrack ( date timestamp, 
timezonename varchar(20), 
countrycode varchar(10), 
division varchar(100), 
city varchar(100), 
location varchar(100), 
route varchar(100), 
activitytype varchar(100), 
mood varchar(100), 
constitution varchar(100), 
intensity varchar(100), 
equipment varchar(200), 
notes varchar(1000), 
tags varchar(200), 
temperature float, 
weather varchar(100), 
winddirection varchar(100), 
windspeed float, 
totalduration float, 
duration float, 
totaldistance float, 
distance float, 
avgspeed float, 
minspeed float, 
maxspeed float, 
ascent float, 
descent float, 
avgheartrate float, 
minheartrate float, 
maxheartrate float, 
calories int8, 
avgcadence float, 
mincadence float, 
maxcadence float, 
avgpower int8, 
minpower int8 , 
maxpower int8, 
totalenergy int8, 
trainingintensity float , 
trainingstresslevel int8, 
trimp int8, 
rating int8 );
CREATE TABLE
rubitrack=> \copy rubitrack ( date , timezonename , countrycode ,
division , city , location , route , activitytype , 
mood , constitution , intensity , equipment , 
notes , tags , temperature , weather , winddirection , 
windspeed , totalduration , duration , totaldistance , 
distance , avgspeed , minspeed , maxspeed , 
ascent , descent , avgheartrate , minheartrate , 
maxheartrate , calories , avgcadence , 
mincadence , maxcadence , avgpower , 
minpower , maxpower , totalenergy , 
trainingintensity , trainingstresslevel , trimp , rating  )  
FROM './RubiTrack.csv'  DELIMITER ';'  CSV HEADER; 
COPY 10445
rubitrack=> \quit


After it’s possible to do test with Grafana :

SELECT
  date_trunc('month',date)::date  AS "time",
  activitytype as metric,
  sum(totaldistance)
FROM rubitrack
WHERE
  $__timeFilter(date)  
  and (activitytype like 'Exercise Biking%' 
  or activitytype like 'Biking%' 
  or activitytype like 'E-Biking%' 
  or activitytype like 'Mountain Biking%' 
  or activitytype like 'BMX%' 
  or activitytype like 'E-Mountain Biking%' )
GROUP BY date_trunc('month',date)::date ,activitytype
ORDER BY date_trunc('month',date)::date  ASC

With sum(totaldistance)/1000

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.