Postgresql between min max in where

Question

with a Postgresql 9.6 table structure like:

who | when

Each day has multiple records with same who and different when. Ipotetically each record is a in or out action for who, so need to get total time in for each who.

i.e.

who | when
  A | 2017-03-01 08:00 
  A | 2017-03-01 12:00
  A | 2017-03-01 13:00
  A | 2017-03-01 15:00

how can I get the total of 6hours in?

I think that max(when) - min(when) gets the period but need to subtract the middle data calculating middle min and max.

So need to get the 12:00 as 'morningout' and 13:00 as 'afternoonin' but when I put the betweeen min max in where it complains

'no aggregate function possible in where'

select who, 
      to_char(date_trunc('day', when), 'YYYY-MM-DD')  "thisday", 
       count(who) as 'signIn'
       min(when) as 'morningout'          
       max(when) as 'afternoonin' 


from the_table 
where when between max(when) and min(when)

group by who, "thisday"
order by who;

Answer 1

You can do this with window functions:

select   who,
         sum("when" - lag)
from     (select row_number() over w,
                 who,
                 "when",
                 lag("when") over w
          from   t
          window w as (partition by who order by "when")) d
where    row_number % 2 = 0
group by who

If you need this per days, just use the date_trunc('day', "when") in the group by clause. You can also put date_trunc('day', "when") in the partition by clause, inside the window definition to avoid pairing which spans across days:

select   who,
         date_trunc('day', "when"),
         sum("when" - lag)
from     (select row_number() over w,
                 who,
                 "when",
                 lag("when") over w
          from   t
          window w as (partition by who, date_trunc('day', "when") order by "when")) d
where    row_number % 2 = 0
group by who, date_trunc('day', "when")

However, these solutions require that rows have to be in in + out pairs. For a more reliable solution, you'll need a direction column.

http://rextester.com/UJWWH59178