postgres - calculating and grouping time difference

Question

I have a DB table called asset_history that looks like this

 id         timestamp           Status       site_name       time_on_site
415 "2019-09-11 02:58:00.000"   "Entered"   "GF-R5-int" 
415 "2019-09-11 02:54:10.584"   "Left"      "GF-R5-int"     "189898"
415 "2019-09-11 02:51:00.687"   "Entered"   "GF-R5-int" 
415 "2019-09-11 02:37:34.136"   "Left"      "GF-R5-int"     "348257"
415 "2019-09-11 02:31:45.88"    "Entered"   "GF-R5-int"

I need to find out the total time spent by asset 415 on the site GF-R5-int. Assuming the current time is 2019-09-11 03:05:00.000, the total time spent by asset 415 on site GF-RT-int would be (348257+189898)ms + (2019-09-11 03:05:00.000 - 2019-09-11 02:58:00.000)ms... Which comes to a total of 888155 ms. Below is the query I have got so far which only sums up the time_on_site values from the Left events. I'm unable to figure out how to add to it the time difference between current time and timestamp from the latest Entered event

select a.asset_id, at.name, count(*), sum(ah.time_spent_on_site)
from asset_history ah left outer join asset a on (ah.asset_id=a.id)
left outer join asset_type at on (a.asset_type=at.id)
where ah.asset_id=415
and ah.status='Left'
and ah.site_name='GF-R5-int'
group by a.asset_id, at.name

If asset_history already contains a column for the time spent on site, why can't you just sum that column? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 11, 2019 at 4:16
Tim - As you can see in my query, I am already doing that. My problem is that I also need to add to that the time difference between current time and the most recent 'Entered' event. The 'Entered' events do not have a 'time_on_site' value present against them. — user1066568
– user1066568, Commented Sep 11, 2019 at 4:17
Please rephrase your question by showing clear sample input, lacking what you need, along with the output you want. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 11, 2019 at 4:18

Jasen · Accepted Answer · 2019-09-11 05:52:46Z

1

maybe like this.

select a.id, at.name, (count(*)+1)/2 as count, sum(ah.time_on_site) + case when count(*)&1 = 1 then  extract( epoch from now()) - extract( epoch from max(timestamp)) else 0 end * 1000    
from asset_history ah left outer join asset a on (ah.asset_id=a.id)
left outer join asset_type at on (a.asset_type=at.id)
where ah.asset_id=415
and ah.site_name='GF-R5-int'
group by a.id, at.name;

If you can be sure that there will always be the correct number of records. a odd number of records means the most recent record is an enetered with no matching exit

answered Sep 11, 2019 at 5:52

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres - calculating and grouping time difference

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related