Extracting sum of time in a period
Using tsrange

I like tsrange. It's certainly not the only way to do this, but it is not error prone and it's easy to read and write.

SELECT
  grp.range,
  sum(
    EXTRACT(epoch FROM least(upper(grp.range),datahora_fim))
    - EXTRACT(epoch FROM greatest(lower(grp.range),datahora_ini))
  )
FROM (
  SELECT
    date_trunc('hour', min(datahora_ini)),
    date_trunc('hour', max(datahora_fim))
  FROM login
) AS bounds(min,max)
CROSS JOIN LATERAL generate_series(min, max, '1 hour') AS gs(start)
CROSS JOIN LATERAL tsrange(gs.start, gs.start + '1 hour') AS grp(range)
JOIN login ON grp.range &amp;&amp; tsrange(datahora_ini,datahora_fim)
GROUP BY range
ORDER BY range;
                     range                     |  sum  
-----------------------------------------------+-------
 ["2017-06-02 08:00:00","2017-06-02 09:00:00") | 17821
 ["2017-06-02 09:00:00","2017-06-02 10:00:00") | 18000
 ["2017-06-02 10:00:00","2017-06-02 11:00:00") | 18000
 ["2017-06-02 11:00:00","2017-06-02 12:00:00") | 17079
 ["2017-06-02 12:00:00","2017-06-02 13:00:00") | 14400
 ["2017-06-02 13:00:00","2017-06-02 14:00:00") | 14363
 ["2017-06-02 14:00:00","2017-06-02 15:00:00") |  3716
 ["2017-06-02 15:00:00","2017-06-02 16:00:00") |  3600
 ["2017-06-02 16:00:00","2017-06-02 17:00:00") |   833
(9 rows)


The first part generates the ranges for the data

SELECT bounds.*, grp.*
FROM (
  SELECT
    date_trunc('hour', min(datahora_ini)),
    date_trunc('hour', max(datahora_fim))
  FROM login
) AS bounds(min,max)
CROSS JOIN LATERAL generate_series(min, max, '1 hour') AS gs(start)
CROSS JOIN LATERAL tsrange(gs.start, gs.start + '1 hour') AS grp(range)
ORDER BY range;
         min         |         max         |                     range                     
---------------------+---------------------+-----------------------------------------------
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 08:00:00","2017-06-02 09:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 09:00:00","2017-06-02 10:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 10:00:00","2017-06-02 11:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 11:00:00","2017-06-02 12:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 12:00:00","2017-06-02 13:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 13:00:00","2017-06-02 14:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 14:00:00","2017-06-02 15:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 15:00:00","2017-06-02 16:00:00")
 2017-06-02 08:00:00 | 2017-06-02 17:00:00 | ["2017-06-02 16:00:00","2017-06-02 17:00:00")
(9 rows)


The second part joins them back to the original data, and


Pulls the seconds since epoch on the least of the high-point on the range and the datahora_fim. The ceiling for the range is the range itself.
Pulls the seconds since epoch on the greatest of the low point on the range, and datahora_ini. The floor for the range is the range itself.
Subtracts the two to get the difference in seconds
Sums it up.


That looks like this,

sum(
  EXTRACT(epoch FROM least(upper(grp.range),datahora_fim))
  - EXTRACT(epoch FROM greatest(lower(grp.range),datahora_ini))
)


This method can use a functional index on tsrange(datahora_ini,datahora_fim).

Your original query just shows the lower part of the tsrange, if you prefer that just use lower(grp.range)