In my database I have two tables:
CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20))
CREATE TABLE IF NOT EXISTS activity( id_activity INTEGER PRIMARY KEY AUTOINCREMENT, id_type INTEGER, start_time TEXT, distance REAL, duration INTEGER, elevation INTEGER, FOREIGN KEY(id_type) REFERENCES type_of_activity(id_type))
Some details: start_time format: "YYYY-MM-DD HH:MM", duration in seconds. I want to make query that summary:
- Every week mileage, duration, maybe from this data calculate pace, and elevation.
- The same as above, but for months
I think that is good to convert the start_time to:
But I don't know how to aggregate results.
Can someone help me?