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;
Para calcularmos a diferença em dias executamos a sentença abaixo:
SELECT
imovel_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_dias
FROM tb_locacao;
Após executarmos a sentença acima, teremos o resultado exibido na tabela abaixo:
SELECT
imovel_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_dia
FROM 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:
imovel_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 intervalo
FROM 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:
3º Exemplo
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;
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.
cliente_id,
data_cadastro,
/*calcula a diferença de dias*/
CURRENT_DATE - data_cadastro AS quantidade_dias
FROM 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:
4º Exemplo
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.
Muito massa, vlw mesmo
ResponderExcluirVlw Mano
ResponderExcluirEste comentário foi removido por um administrador do blog.
ResponderExcluirExemplo para o leitor que queria incluir o primeiro dia na contagem da diferença
ResponderExcluir1º EXEMPLO - DIFERENÇA ENTRE 01/01/2015 a 31/01/2015 = diferença de 31 dias.
SELECT DATE '2015-01-31' - (DATE '2015-01-01' - INTERVAL '1 DAY') AS DIFERENCA_DATA
Retorna '31 days' , mas o tipo de dado é INTERVAL, caso queira retornar um valor inteiro execute a sentença do 2º exemplo.
2º EXEMPLO - DIFERENÇA DE DATA CONSIDERANDO O PRIMEIRO DIA E RETORNANDO INTEIRO
SELECT DATE '2015-01-31' - DATE (DATE '2015-01-01' - INTERVAL '1 DAY') DIFERENCA_DATA_CONSIRANDO_PRIMEIRO_DIA
Kelly, de coração.. muito obrigado mesmo por este post
ResponderExcluirObrigado por me ajudar, se não fosse por você estaria em apuros.
ResponderExcluirBom dia, gostaria de saber como que fica pra mostrar todos os dias como colunas quando eu passar um período na consulta.
ResponderExcluirExemplo:
Preciso fazer uma reserva e quero ver a disponibilidade dos quartos, dai eu entro com a dataInicial e dataFinal e na consulta mostra como colunas os dias que percorrem entre esse periodo e me diga qual quarto está reservado e qual disponivel. É possível isso ?
Desde já meu muito obrigado.
3 days 16:51:54 = 3* 24 h + 16:51:54 h = 88:51:54, como faço para deixar no formato em Horas?
ResponderExcluirAlgum exemplo de como calcular a diferença de dias entre linha atual e data da linha anterior ?
ResponderExcluirBoa tarde, segue o exemplo de como calcular a diferença de dias entre a data da linha atual e data da linha anterior.
Excluir-No 1º Exemplo: o campo data é do tipo date.
-No 2º Exemplo: o campo data_hora do tipo timestamp, ou seja, data e hora.
Talvez um deles te ajude, segue o exemplo.
/**
*1º Exemplo
*/
/**
*Cria a tabela de venda tb_vendas_ex01
*/
CREATE TABLE tb_vendas_ex01
(
cliente_id integer,
data date
);
/**
*Insere os registros na tabela tb_vendas_ex01
*/
INSERT INTO tb_vendas_ex01 VALUES
(540, '2019-04-09'),
(541, '2020-06-10'),
(541, '2020-06-05'),
(542, '2020-08-05'),
(543, '2020-08-07'),
(544, '2020-08-14'),
(545, '2020-08-21'),
(546, '2020-09-07'),
(547, '2020-09-10'),
(548, '2020-09-24'),
(549, '2020-10-15'),
(550, '2020-10-20'),
(541, '2020-10-25');
/**
*Visualiza os registros na tabela tb_vendas_ex01
*/
SELECT * FROM tb_vendas_ex01;
/**
*Calcula o intervalo entre as vendas
*/
SELECT
cliente_id,
data,
data - LAG(data) OVER (ORDER BY data) AS intervalo_em_dias_tipo_inteiro,
AGE
(
data, LAG(data) OVER (ORDER BY data)
) AS intervalo_tipo_interval_yy_mm_dd_hh_mm_ss,
TO_CHAR
(
AGE
(
data, LAG(data) OVER (ORDER BY data)
),
'YY "ano(s)" MM "mes(es)" DD "dia(s)"'
) AS intervalo_format_yy_mm_dd_hh_mm_ss
FROM tb_vendas_ex01;
/**
*2º Exemplo
*/
/**
*Cria a tabela de venda tb_vendas_ex02
*/
CREATE TABLE tb_vendas_ex02
(
cliente_id integer,
data_hora timestamp without time zone
);
/**
*Insere os registros na tabela tb_vendas_ex02
*/
INSERT INTO tb_vendas_ex02 VALUES
(540, '2019-04-20 11:32:01'),
(541, '2020-05-23 11:32:01'),
(541, '2020-08-05 12:31:01'),
(542, '2020-08-06 08:20:01'),
(543, '2020-08-07 23:36:45'),
(544, '2020-08-07 23:47:13'),
(545, '2020-08-07 23:58:17'),
(546, '2020-08-08 08:05:19'),
(547, '2020-08-08 08:15:27'),
(548, '2020-08-08 09:50:08'),
(549, '2020-08-09 01:00:06'),
(550, '2020-08-09 21:00:05'),
(541, '2020-08-10 15:31:01');
/**
*Visualiza os registros na tabela tb_vendas_ex02
*/
SELECT * FROM tb_vendas_ex02;
/**
*Calcula o intervalo entre as vendas
*/
SELECT
cliente_id,
data_hora,
data_hora - LAG(data_hora) OVER (ORDER BY data_hora) AS intervalo_dd_hh_mm_ss,
EXTRACT( day FROM data_hora - LAG(data_hora) OVER (ORDER BY data_hora)) AS extrair_so_qtd_dias,
AGE
(
data_hora, LAG(data_hora) OVER (ORDER BY data_hora)
) AS intervalo_yy_mm_dd_hh_mm_ss,
TO_CHAR
(
AGE
(
data_hora, LAG(data_hora) OVER (ORDER BY data_hora)
),
'YY "ano(s)" MM "mes(es)" DD "dia(s)" HH24 "HORA(S)" MI "MINUTO(S)" SS "SEGUNDOS(S)"'
) AS intervalo_format_yy_mm_dd_hh_mm_ss
FROM tb_vendas_ex02;
show!
ResponderExcluirTop demais, obrigado
ResponderExcluirEu tenho uma duvida, supomos que tenho vários registro de inicio e fim de conexões, por exemplo:
ResponderExcluirID data_inicio data_fim
1;"2020-11-03"; "2020-11-07"
2;"2020-11-03"; "2020-11-07"
3;"2020-10-26"; "2020-11-07"
4;"2020-10-26"; "2020-11-07"
5;"2020-11-07"; "2020-11-07"
6;"2020-11-06"; "2020-11-07"
No primeiro registro, a conexao ficou ativa entre 03/11 e 07/11,
logo os dias 4, 5 e 6 ela estava ativa tambem.
Ai eu queria saber, como saber quantas conexoes estavam ativas em cada dia do mes? fazendo isso usando somente select.