SQLLite query datatime divided into week of the year, and month of the year

date formatsqlite

In my database I have two tables:

type_of_activity

CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20))

activity

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:

  1. Every week mileage, duration, maybe from this data calculate pace, and elevation.
  2. The same as above, but for months

I think that is good to convert the start_time to:
in 1)strftime('%Y-%W',start_time); 2)strftime('%Y-%m',start_time)
But I don't know how to aggregate results.

Can someone help me?

Best Answer

I made it

SELECT 
  strftime('%Y-%m',star_time) AS date
  , Sum(distance) 
FROM activity 
WHERE 
Group By 
  strftime('%Y-%m',start_time)
Related Question