我有一个登录表,它记录了datahora_ini
(系统进入时间)和datahora_fim
(系统退出时间)。样本数据:
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' )
;
我需要设置一个查询,我可以在其中添加当时登录到系统的所有用户的时间!所需格式的虚假示例:
time time_logged ( Seconds )
07:00 1005
08:00 4980
09:00 8193
10:00 9259
11:00 25692
12:00 18823
我当前的查询:
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;