CODEWITHSUNDEEP
postgresqltimestamprowsmultiple-columnsdatediff
user2067767
user2067767

Reputation: 3

Difference between dates in different rows

Hy

my problem is, that I need the average time between a chargebegin & chargeend row (timestampserver) grouped by stationname and connectornumber and day.

The main problem is, that i can not use a Max oder Min function because I have the same stationname/connecternumber combination several times in the table.

So in fact I have to select the first chargebegin and find the next chargeend (the one with the same station/connectornumber combination and the min(id) > chargebegin.id) to get the difference.

I tried a lot but in fact i have no idea how to do this.

Database is postgresql 9.2

Testdata:

create table datatable (
    id int,
    connectornumber int,
    message varchar,
    metercount int,
    stationname varchar,
    stationuser varchar,
    timestampmessage varchar,
    timestampserver timestamp,
    authsource varchar
);

insert into datatable values (181,1,'chargebegin',4000,'100','FCSC','2012-10-10 16:39:10','2012-10-10 16:39:15.26');
insert into datatable values (182,1,'chargeend',4000,'100','FCSC','2012-10-10 16:39:17','2012-10-10 16:39:28.379');
insert into datatable values (184,1,'chargebegin',4000,'100','FCSC','2012-10-11 11:06:31','2012-10-11 11:06:44.981');
insert into datatable values (185,1,'chargeend',4000,'100','FCSC','2012-10-11 11:16:09','2012-10-11 11:16:10.669');
insert into datatable values (191,1,'chargebegin',4000,'100','MSISDN_100','2012-10-11 13:38:19','2012-10-11 13:38:26.583');
insert into datatable values (192,1,'chargeend',4000,'100','MSISDN_100','2012-10-11 13:38:53','2012-10-11 13:38:55.631');
insert into datatable values (219,1,'chargebegin',4000,'100','MSISDN_','2012-10-12 11:38:03','2012-10-12 11:38:29.029');
insert into datatable values (220,1,'chargeend',4000,'100','MSISDN_','2012-10-12 11:40:14','2012-10-12 11:40:18.635');

Upvotes: 0

Views: 200

Answers (1)

Tomas Greif
Tomas Greif

Reputation: 22661

This might have some syntax errors as I can't test it right now, but you should get an idea, how to solve it.

with 
chargebegin as (
select
 stationname,
 connectornumber,
 timestampserver,
 row_number() over(partition by stationname, connectornumber order by timestampserver) as rn
from
 datatable
where
 message = 'chargebegin'
),
chargeend as (
select
 stationname,
 connectornumber,
 timestampserver,
 row_number() over(partition by stationname, connectornumber order by timestampserver) as rn
from
 datatable
where
 message = 'chargeend'
)

select
 stationname,
 connectornumber,
 avg(b.timestampserver - a.timestampserver) as avg_diff
from
 chargebegin a 
 join chargeend b using (stationname, connectornumber, rn)
group by
 stationname,
 connectornumber

This assumes that there is always end event for begin event and that these event cannot overlap (means that for stationname and connectornumber, there can be only one connection at any time). Therefore you can user row_number() to get matching begin/end events and then do whatever calculation is needed.

Upvotes: 1

Related Questions