CODEWITHSUNDEEP
postgresqltimestampgrouping
user1066568
user1066568

Reputation: 747

postgres - calculating and grouping time difference

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

Upvotes: 0

Views: 60

Answers (1)

Jasen
Jasen

Reputation: 12412

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

Upvotes: 1

Related Questions