CODEWITHSUNDEEP
postgresqldateaggregate-functions
Jayant
Jayant

Reputation: 323

Getting data from postgres weekly (according to date)

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

Upvotes: 4

Views: 3452

Answers (3)

CFreitas
CFreitas

Reputation: 1775

Something like this (assuming that by timestamp you mean the data type timestamp). In order to make the 1st day of the week to be Sunday, I added and extra day to "date" in the group by.

select (start_date - date_part('dow', start_date) * interval '1 day')::date start_date,
       (start_date + (6 - date_part('dow', start_date)) * interval '1 day')::date end_date,
        total_time_spent
from (
    select min("date") start_date, sum(timespent) total_time_spent
    from mytable
    where user=u1
    group by date_part('year', "date"), date_part('week', "date" + interval '1 day')) "tmp"

order by start_date

This is a more generic approach, for any date interval.

Upvotes: 0

klin
klin

Reputation: 121534

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)

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select
    ui,
    date_trunc('week', the_date)::date as start_date,
    date_trunc('week', the_date)::date + 6 as end_date,
    sum(timespent) as "sum"
from t
group by 1, 2, 3
order by 1,2

Upvotes: 1

Related Questions