Tecnologia da Informação
Postgresql - diferença entre datas
Neste post vamos apresentar 4 exemplos de como calcular a diferença entre datas.
Caso tenha interesse, faça o download ou veja o script no Github.
Para calcular a diferença entre duas datas, em dias, no postgresql utilizamos o operador aritmético menos "-".
data_final - data_inicial
Para calcular a diferença entre duas datas, em dias, meses e anos utilizamos a função age.
age(data_final, data_inicial)
1º Exemplo
Cenário: em uma imobiliária, um funcionário precisa fazer um levantamento de
quantos dias são necessários para alugar os imóveis. Para fazer este cálculo vamos utilizar a tabela "tb_locacao". Esta tabela possui três colunas:
- imovel_id: número de identificação do imóvel;
- data_oferta: data de início da oferta do imóvel;
- data_locacao: a data em que o imóvel foi alugado;
Veja a imagem da tabela "tb_locacao":
Solução: temos que calcular a diferença entre as colunas "data_locação" e "data_oferta":
Para calcularmos a diferença em dias executamos a sentença abaixo:
SELECTimovel_id,data_oferta,data_locacao,/*Calculo da diferença entre a data da locação e a data de oferta*/data_locacao - data_oferta AS quantidade_diasFROM tb_locacao;
Após executarmos a sentença acima, teremos o resultado exibido na tabela abaixo:
Caso seja necessário incluir o primeiro dia na contagem da diferença, subtraia o intervalo de 1 dia da coluna "data_oferta" (data inicial) . Veja a sentença abaixo:
SELECTimovel_id,data_oferta,data_locacao,data_locacao - data_oferta AS quantidade_dias,/*Calculo da diferença entre a data da locação e a data de oferta, incluindo na contagem o primeiro dia*/data_locacao - (data_oferta - INTERVAL '1 DAY') :: DATE AS dif_dias_primeiro_diaFROM tb_locacao;
Após executarmos a sentença acima, teremos o resultado exibido na tabela abaixo:
Observação: perceba que quando subtraímos datas o primeiro dia não é considerado, se precisarmos considerar o 1º dia devemos descontar um dia data inicial utilizando "INTERVAL".
2º Exemplo
Cenário: exibir o período necessário para alugar os imóveis (idêntico ao exemplo anterior), mas no seguinte formato.
x anos y meses z dias
Para calcularmos a diferença no formato acima, executamos a sentença a seguir:
SELECTimovel_id,data_oferta,data_locacao,/*Calculo da diferença entre a data da locação e a data de oferta*/AGE(data_locacao, data_oferta) AS intervaloFROM tb_locacao;
Após executar a sentença acima é exibido o seguinte resultado:
O intervalo é exibido em inglês. Para exibir o formato em português utilizamos a função to_char para formatar o resultado da função age. Para conhecer mais sobre a função to_char, leia o artigo PostgreSql - Formatar data
TO_CHAR(valor, formato)
Veja abaixo alguns formatos:
FORMATOS QUE PODEM SER UTILIZADOS NA FUNÇÃO TO_CHAR |
Formato | Descrição |
YYYY | ano |
YY | 2 últimos digitos do ano |
MM | Mês de 01 à 12 |
DD | Dia de 01 à 31 |
HH | Hora do dia de 01 à 12 |
HH12 | Hora do dia de 01 à 12 |
HH24 | Hora do dia de 01 à 24 |
MI | Minuto do dia de 01 à 59 |
SS | Segundo do dia de 01 à 59 |
MS | Milissegundo do dia de 000 à 999 |
US | Micro-segundo do dia de 000000 à 999999 |
MONTH | Nome do mês em maiúsculo. Exemplo: JANEIRO |
month | Nome do mês em minúsculo. Exemplo: janeiro |
Month | Nome do mês com a primeira letra em maiúsculo. Ex.: Janeiro |
DAY | Nome do dia em maiúsculo. Ex.: SEGUNDA |
day | Nome do dia em minúsculo. Ex.: segunda |
Day | Nome do dia com a primeira letra em maiúsculo. Ex.: Segunda |
D | Dia da semana de 1 à 7. Domingo = 1 e Sábado = 7. |
DDD | Dia do ano de 1 à 365 ou 366 (bissexto). |
WW | Dia da semana de 1 à 53. |
Consulte outros formatos no manual do postgresql.
A sentença abaixo formatara o intervalo em dia, mês e ano.
SELECT
imovel_id,
data_oferta,
data_locacao,
/*Calculo da diferença entre a data da locação e a data de oferta*/TO_CHAR(AGE(data_locacao, data_oferta), 'YY "ano(s)" MM "mes(es)" DD "dia(s)"') AS intervalo
FROM tb_locacao;
Perceba que
"ano(s)",
"mes(es)" e
"dias" estão entre aspas duplas para que a função to_char não os converta e ocorra um erro.
Após a execução da sentença, será exibido o seguinte resultado:
Cenário: uma loja virtual vai oferecer uma oferta especial, aos clientes que estão cadastrados a mais de 90 dias no seu site. Para fazer este cálculo vamos utilizar a tabela "tb_cadastro". Esta tabela possui duas colunas:
- cliente_id: número de identificação do cliente;
- data_cadastro: data de cadastro do cliente;
Veja a imagem da tabela "tb_cadastro":
Solução: temos que calcular a diferença entre o dia atual (hoje) e a coluna "data_cadastro". Antes de efetuarmos o cálculo devemos saber como retornar o dia atual.
Para retorna o dia atual utilizamos a função:
CURRENT_DATE
Para exibir o dia atual execute a sentença abaixo (quando executei esta sentença o dia era 04/03/2014):
SELECT CURRENT_DATE;
Agora que já conhecemos a função que calcula o dia atual (hoje), vamos executar a sentença para verificar os clientes que estão cadastrados a mais de 90 dias.
SELECT cliente_id,data_cadastro,/*calcula a diferença de dias*/CURRENT_DATE - data_cadastro AS quantidade_diasFROM tb_cadastro/*filtra somente os clientes que são cadastrados há mais que 90 dias*/WHERE CURRENT_DATE - data_cadastro > 90;Após executarmos a sentença acima, filtramos os clientes que estão cadastrados a mais de 90 dias. Veja a imagem na tabela abaixo:
Cenário: uma fábrica quer saber quais os equipamentos ficaram por um período maior igual a 80 horas em manutenção. Vamos utilizar a tabela "tb_manutencao" para fazer esta consulta, veja a imagem abaixo:
Esta tabela possui 3 colunas:
- equipamento_id: número de identificação de equipamento;
- data_inicio: data de inicial de manutenção do equipamento;
- data_final: data final de manutenção do equipamento;
Após executarmos a sentença abaixo, filtramos os equipamentos que ficaram em manutenção por 80 ou mais horas.
SELECT
equipamento_id,
data_inicio,
data_final,
AGE(data_final, data_inicio)FROM tb_manutencao
WHERE
AGE(data_final, data_inicio) >=
'80 hour'Veja o resultado na imagem abaixo:
Repare que o intervalo é retornado em dias e horas.
1 day = 24 horas
Para comprovar que os intervalos do primeiro e segundo registros tem um período igual ou superior a 80 horas, vamos efetuar o calculo:
Primeiro registro:
3 days 16:51:54 = 3* 24 h + 16:51:54 h = 88:51:54
Segundo registro:
3 days 13:03:55 = 3* 24 h + 13:03:55 h = 85:03:55
Em breve postarei mais exemplos de funções com data.
loading...
-
Postgresql - Função Lag - Diferença De Tempo Para Uma Mesma Coluna
Vamos apresentar 4 exemplos de como calcular a diferença de período para uma mesma coluna. Para isto vamos utilizar a função LAG do PostgreSql. Está função pertence ao grupo das Window Functions e funciona a partir do PostgreSQL 8.4.22 Caso tenha...
-
Subtrair Intervalo De Uma Data
Vamos mostrar neste artigo, como subtrair um período de uma data. Descrevemos 3 exemplos: 1º exemplo: mostra como subtrair um período de dias de uma data. São descritas duas soluções para resolver este problema. A primeira utiliza a subtração...
-
Postgresql - Adicionar Um Intervalo A Uma Data / Hora
Vamos mostrar neste artigo, como acrescentar um período a uma data. Descrevemos 5 exemplos: Caso tenha interesse, faça o download do exemplo, ou veja no GitHub. 1º exemplo: mostra como acrescentar um período em dias a uma data. São descritas duas...
-
Postgresql - Extrair Parte De Uma Data Com Extract
A função extract extrai parte de uma data ou intervalo. Ela é equivalente a função date_part. Vamos descrever 3 exemplos com a função extract. Caso tenha interesse faça download dos exemplos ou veja no GitHub. 1º Exemplo: vamos extrair partes...
-
Postgresql - Formatar Data
Serão descritos 2 exemplos de como formatar uma data. 1º Exemplo: exibir a data no formato dd/mm/aaaa (dia/mês/ano): Solução: vamos utilizar a função to_char para formatar a data. Na imagem abaixo, vemos a tabela "tb_fornecedor", vamos...
Tecnologia da Informação