Overlapping time intervals

Moderator: NorbertKrupa

Post Reply
PerS
Newbie
Newbie
Posts: 4
Joined: Fri Sep 16, 2016 8:15 am

Overlapping time intervals

Post by PerS » Fri Sep 16, 2016 8:28 am

I have a login log from which I want to derive the first time a user logged in through a month plus n days.
For a single period this is of course very easy, but I want an otput like:

User-ID, Period Start, Period End, Min Log in time
1, 1/1/2016,2/10/2016,1/12/2016 03:24:12
1,2/1/2016,3/10/2016,2/5/2016 12:43:40

This means the first period goes from January 1 to February 10 and the second period from February 1 to March 10.
Input log is just User-ID and Log in time.

Best regards!
/P

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: Overlapping time intervals

Post by scutter » Mon Sep 19, 2016 2:18 am

Something like this?

Code: Select all

create table activity(userid integer, login_ts timestamp);
insert into activity values(1, '1/12/2016 03:24:12');
insert into activity values(1, '2/5/2016 12:43:40');
insert into activity values(1, '1/13/2016 09:00');
insert into activity values(1, '1/23/2016 09:00');
insert into activity values(1, '1/31/2016 09:00');
insert into activity values(1, '2/05/2016 09:00');
insert into activity values(1, '2/15/2016 09:00');
insert into activity values(1, '3/05/2016 09:00');
insert into activity values(2, '1/10/2016 09:00');
insert into activity values(2, '1/20/2016 09:00');
insert into activity values(2, '1/30/2016 09:00');
insert into activity values(2, '2/15/2016 09:00');
commit;

create table dates(period_start date);
insert into dates values ('2016-01-01');
insert into dates values ('2016-02-01');
insert into dates values ('2016-03-01');
commit;

select 
	userid, 
	min(period_start) as "Period Start", 
	max(period_end) as "Period End",
	min(login_ts) as "First Login Time",
	max(login_ts) as "Last Login Time"
from activity a 
join 
(
	select period_start, date(add_months(period_start, 1) + '12 days'::interval) as period_end from dates
) q
on login_ts between period_start and period_end
group by userid, period_start
order by userid, period_start;

userid | Period Start | Period End |  First Login Time   |   Last Login Time   
--------+--------------+------------+---------------------+---------------------
      1 | 2016-01-01   | 2016-02-13 | 2016-01-12 03:24:12 | 2016-02-05 12:43:40
      1 | 2016-02-01   | 2016-03-13 | 2016-02-05 09:00:00 | 2016-03-05 09:00:00
      1 | 2016-03-01   | 2016-04-13 | 2016-03-05 09:00:00 | 2016-03-05 09:00:00
      2 | 2016-01-01   | 2016-02-13 | 2016-01-10 09:00:00 | 2016-01-30 09:00:00
      2 | 2016-02-01   | 2016-03-13 | 2016-02-15 09:00:00 | 2016-02-15 09:00:00
(5 rows)


Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

PerS
Newbie
Newbie
Posts: 4
Joined: Fri Sep 16, 2016 8:15 am

Re: Overlapping time intervals

Post by PerS » Wed Oct 12, 2016 12:45 pm

Thanks Sharon, problem solved! :shock:

Post Reply

Return to “Vertica SQL”