How to calculate shift hours in postgres? I have table like below where (I=In) and (O=Out):


 id |    date    |   time   | status | employee_id 
----+------------+----------+--------+-------------
  1 | 2015-07-01 | 11:00:00 | O      |           1
  2 | 2015-07-01 | 08:00:00 | I      |           1
  3 | 2015-07-01 | 16:00:00 | O      |           1
  4 | 2015-07-01 | 12:00:00 | I      |           1
  5 | 2015-07-02 | 16:30:00 | O      |           1
  6 | 2015-07-02 | 10:12:00 | I      |           1
 

I want to calculate attendance grouped by date, employee_id and with shift hours like below:


    date    | employee_id |   min    |   max    |  delta
------------+-------------+----------+----------+----------
 2015-07-01 |           1 | 08:00:00 | 16:30:00 | 07:00:00      &lt;--(shift)
 2015-07-02 |           1 | 10:12:00 | 16:30:00 | 06:18:00


I don't know if it can be possible to do it in postgresql.

Appreciate your support and help.
Assuming your status is consistent, i.e. always comes in pairs you could do something like:

select dt, employee_id, min_tm, max_tm, sum(delta_tm)
from (
    select dt
         , employee_id 
         , min(tm) over (partition by employee_id,dt) as min_tm
         , max(tm) over (partition by employee_id,dt) as max_tm
         , tm - lag(tm) over (partition by employee_id,dt order by tm) delta_tm
         , status
    from t
) as u 
where status = 'O'
group by dt, employee_id, min_tm, max_tm;