I have a login table, which registers datahora_ini
(system entry time) and datahora_fim
(system exit time). Sample data:
CREATE TABLE login(cod_user,datahora_ini,datahora_fim)
AS
VALUES
( 101::int, '2017-06-02 08:02:14'::timestamp, '2017-06-02 13:59:23'::timestamp ),
( 102, '2017-06-02 08:03:38', '2017-06-02 16:13:53' ),
( 103, '2017-06-02 08:05:50', '2017-06-02 11:44:39' ),
( 104, '2017-06-02 08:06:03', '2017-06-02 14:01:33' ),
( 105, '2017-06-02 08:06:35', '2017-06-02 14:00:23' ),
( 106, '2017-06-02 08:10:05', '2017-06-02 08:31:26' )
;
I need to set up a query where I can add the time of all the users that were logged into the system at that time! Fake example of the desired format:
time time_logged ( Seconds )
07:00 1005
08:00 4980
09:00 8193
10:00 9259
11:00 25692
12:00 18823
My current query :
SELECT
TO_CHAR(al.datahora_fim,'HH24:00') as Data_ini,
ROUND(SUM(CASE WHEN al.datahora_fim IS NULL THEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - al.datahora_ini)) ELSE EXTRACT(EPOCH FROM (al.datahora_fim - al.datahora_ini)) END))as tempo_logado
FROM callcenter.agente_login al
LEFT JOIN callcenter.agente a ON ( a.id_agente = al.id_agente )
LEFT JOIN crm.usuarios u ON ( a.crm_cod_usuario = u.cod_usuario )
WHERE ( al.datahora_fim BETWEEN '2017-06-02 00:00:00' AND '2017-06-02 23:59:59' )
GROUP BY Data_ini
ORDER BY Data_ini ASC;
Best Answer
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.
The first part generates the ranges for the data
The second part joins them back to the original data, and
datahora_fim
. The ceiling for the range is the range itself.datahora_ini
. The floor for the range is the range itself.That looks like this,
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 uselower(grp.range)