Getting data from postgres weekly (according to date)

Question

user  timespent(in sec)  date(in timestamp)
u1       10                t1(2015-08-15)
u1       20                t2(2015-08-19)
u1       15                t3(2015-08-28)
u1       16                t4(2015-09-06)

Above is the format of my table, which represents timespent by user on a course and it is ordered by timestamp. I want to get sum of timespent by a particular user, say u1 weekly in the format :

start_date    end_date       sum
2015-08-15   2015-08-21      30
2015-08-22   2015-08-28      15
2015-08-29   2015-09-04      0
2015-09-05   2015-09-11      16

Answer 1

The difficulty lies in the fact that the seven-day periods that you want to get are not regular weeks starting with Monday. You can not therefore use standard functions to get the week number based on the date, and have to use your own weeks generator using generate_series().

Example data:

create table sessions (user_name text, time_spent int, session_date timestamp);
insert into sessions values
('u1', 10, '2015-08-15'),
('u1', 20, '2015-08-19'),
('u1', 15, '2015-08-28'),
('u1', 16, '2015-09-06');

The query for an arbitrary chosen period from 2015-08-15 to 2015-09-06:

with weeks as (
    select d::date start_date, d::date+ 6 end_date
    from generate_series('2015-08-15', '2015-09-06', '7d'::interval) d
    )
select w.start_date, w.end_date, coalesce(sum(time_spent), 0) total
from weeks w
left join (
    select start_date, end_date, coalesce(time_spent, 0) time_spent
    from weeks
    join sessions
    on session_date between start_date and end_date
    where user_name = 'u1'
    ) s
on w.start_date = s.start_date and w.end_date = s.end_date
group by 1, 2
order by 1;

 start_date |  end_date  | total
------------+------------+-------
 2015-08-15 | 2015-08-21 |    30
 2015-08-22 | 2015-08-28 |    15
 2015-08-29 | 2015-09-04 |     0
 2015-09-05 | 2015-09-11 |    16
(4 rows)