Cálculo com horas no Postgres

Olá galera,

Esses dias uma pessoa me mandou e-mail, pedido minha ajuda.

Vocês já sabem que estou sempre disposto a ajudar, muitas vezes

não dou a resposta, mas explico e mostro as ferramentas que auxiliam no desenvolvimento do problema.

Dê um peixe a um homem faminto e você o alimentará por um dia. Ensine-o a pescar, e você o estará alimentando pelo resto da vida. Provérbio chinês

Isso é algo, que tenho por base de pensamento em determinadas situações.
Mas como a dúvida que me enviaram eu vi que era um pouco complexa se tratando de POSTGRES! 

Enfim, vamos lá.

Foi apresentado o seguinte problema.

Uma criança vai ao parque de diversões se divertir, ela paga R$ 15,00 para entrar, ela fica no parque desde das 10:30h até as 15:37h. Qual é o tempo total que ela ficou no parque?

Primeiramente parece ser algo simples, é um conta de menos, onde subtrai o horário de saída com o horário de entrada.
Mas nem sempre é assim, no ramo de informática, principalmente se tratando de Postgres, no SQL Server é algo muito simples, gasta-se 1 linha de código para ser feito.

Vamos lá, nós temos a seguinte tabela

ID -> Identificação sequencial de entradas e saídas do parque
HORA_ENTRADA -> Hora que a pessoa entrou
HORA_SAIDA -> Hora que a pessoa saiu


CREATE TABLE PLAYGROUND(
ID integer,
HORA_ENTRADA timestamp,
HORA_SAIDA timestamp
);

Tipo Primitivo TimeStamp -> Formato que acompanha Data e Hora

Vamos inserir alguns registros, pensando que entrou 3 pessoas no Parque


INSERT INTO PLAYGROUND VALUES(1,'2012-11-19 14:00:00','2012-11-19 15:30:00');
INSERT INTO PLAYGROUND VALUES(2,'2012-11-19 12:00:32','2012-11-19 17:30:51');
INSERT INTO PLAYGROUND VALUES(3,'2012-11-19 9:42:01','2012-11-19 14:50:51');

Mostrando todos registros inseridos

SELECT * FROM PLAYGROUND;

Temos abaixo o seguinte resultado








Vamos fazer o cálculo de tempo total que a pessoa permaneceu no local


SELECT 
*,
(to_timestamp(to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS') 
- 
to_timestamp(to_char(HORA_ENTRADA,'HH24:MI:SS'),'HH24:MI:SS')) AS     TEMPO_PERMANECIDO,


FROM PLAYGROUND 
ORDER BY ID;

Temos abaixo o resultado da pesquisa

Lembrando que não é preciso criar um campo para armazenar o tempo total, pois viola as regras da 3ª Forma Normal. Se o campo pode ser obtido por algum cálculo não é necessário que ele faça parte da tabela.

Vamos explicar passo a passo

to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS')  -> Converte campo para o formato de texto do tipo Char com a seguinte formatação HH24:MI:SS que é Horas to tipo 24h (pode ser 12h) e depois converte essa conversão do tipo Char para novamente TimeStamp (to_timestamp(to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS') convertendo também para o formato de 24h.
Também foi usado o comando AS do SQL que é apelidar o nome do campo.

Ok!

Vamos detalhar mais essa situação, agora vamos obter o tempo máximo permanecido e o tempo minimo permanecido no local.


SELECT 
*,
(to_timestamp(to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS') 
- 
to_timestamp(to_char(HORA_ENTRADA,'HH24:MI:SS'),'HH24:MI:SS')) AS TEMPO_PERMANECIDO,

(SELECT MAX(to_timestamp(to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS') 
- 
to_timestamp(to_char(HORA_ENTRADA,'HH24:MI:SS'),'HH24:MI:SS')) 
FROM PLAYGROUND) AS MAIOR_TEMPO_PERMANECIDO,

(SELECT MIN(to_timestamp(to_char(HORA_SAIDA,'HH24:MI:SS'),'HH24:MI:SS') 
- 
to_timestamp(to_char(HORA_ENTRADA,'HH24:MI:SS'),'HH24:MI:SS')) 
FROM PLAYGROUND) AS MENOR_TEMPO_PERMANECIDO

FROM PLAYGROUND 
ORDER BY ID;

Vejam, foi usado mais dois SubSelects que mostra o tempo Maximo com a função MAX e o tempo Minimo com a função MIN.

O Subselect funciona quando queremos retornar apenas um resultado da pesquisa, ou seja, aqui queremos retorna apenas o maior tempo e o menor tempo

Temos abaixo o resultado da pesquisa

Lembrando que todo esse processo poderia ser feito no próprio Java, mas se o banco de dados pode fazer isso, é melhor ainda, vai economizar muitas e muitas linhas de código para fazer isso no java..

A operação que pode ser feita, pode ser qualquer operação aritimética + , - , * , /
Esse código pode diminuir, tambem é possivel implementar várias outras funções como, mostrar a média,
para isso só usar mais SubSelect.

É isso aí gente, calculando com Postgres..