Translate

quarta-feira, 2 de abril de 2014

Postgresql - Subtrair intervalo de uma data

Vamos mostrar neste artigo, como subtrair um período de uma data. Descrevemos 4 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 de um número inteiro e a segunda utiliza a subtração de um intervalo (interval);
  • 2º exemplo: mostra como subtrair um período de meses de uma data. Além de listar outros períodos que podem ser utilizados;
  • 3º exemplo: mostra como subtrair um período de dias a partir da data atual (hoje);
  • 4º exemplo: mostra como subtrair um período de meses de uma data. Só que neste exemplo o período se encontra em um coluna do tipo interval (armazena um intervalo de tempo). 
Caso tenha interesse, faça o download dos exemplos, ou veja no GitHub.

1º Exemplo

Um mercado deve recolher os produtos das prateleiras três dias antes da data de vencimento. Para calcular a data de recolhimento dos produtos vamos utilizar a tabela "tb_produtos".

tb_produtos
Descrição das colunas
  • produto_id: identificação do produto;
  • data_vencimento: data de vencimento do produto;
Temos duas formas para resolver este exemplo.

1ª Solução

Para subtrairmos dias de uma data vamos utilizar o operador "-".

data n

Onde n: número de dias

Para exibir as datas de recolhimento, vamos executar a consulta abaixo:

SELECT
produto_id,
data_vencimento,
data_vencimento - 3 AS data_recolhimento
FROM tb_produtos;

Após a execução da sentença, será exibida a data de recolhimento. Veja na imagem a seguir.


Caso haja necessidade, formate as datas através do comando to_char. Veja o comando abaixo:

SELECT
produto_id,
to_char (data_vencimento, 'dd/mm/yyyy'),
to_char (data_vencimento - 3, 'dd/mm/yyyy') AS data_recolhimento
FROM tb_produtos;

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.



Caso queira saber mais sobre formatação de data veja o artigo:
PostgreSql - Formatar data

2ª Solução

Podemos subtrair um intervalo de uma data.

 data - inverval 'n período '
Onde:
  • inverval: tipo de dado que armazena um intervalo de tempo no PostgreSql;
  • n: valor numérico;
  • período: armazena unidades de medida de tempo. Elas são:
    • year / years: intervalo em anos. Ex.: 1 year, 2 years, 3 years ...
    • month / months / mon / mons: intervalo em meses. Ex.: 1 month, 2 months, 3 months ... Também podemos utilizar a unidade abreviada. Ex.: 1 mon, 2 mons, 3 mons ...
    • day / days: intervalo em dias. Ex.: 1 day, 2 days, 3 days ...
    • hour / hours: intervalo em horas. Ex.: 1 hour, 2 hours, 3 hours ...
    • minute / minutes / min / mins: intervalo em minutos. Ex.: 1 minute, 2 minutes, 3 minutes ... Também podemos utilizar a unidade abreviada. Ex.: 1 min, 2 mins, 3 mins ...
    • second / seconds / sec / secs: intervalo em segundos. Ex.: 1 second, 2 seconds, 3 seconds ... Também podemos utilizar a unidade abreviada. Ex.: 1 sec, 2 secs, 3 secs ...
    • millisecond / millisecond: intervalo em milissegundos. Ex.: 1 millisecond, 2 milliseconds, 3 milliseconds ...
    • microsecond / microseconds: intervalo em microssegundos. Ex.: 1 microsecond, 2 microseconds, 3 microseconds ...

Para resolver este exemplo vamos utilizar a unidade de tempo days, pois queremos subtrair dias.

Para exibir as datas de recolhimento, vamos executar a consulta abaixo:

SELECT
produto_id,
data_vencimento,
data_vencimento - interval '3 days' AS data_recolhimento
FROM tb_produtos;

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.



Perceba que quando subtraimos um intervalo de uma data, será retornado além da data a hora (timestamp).

Caso haja necessidade, formate as datas através do comando to_char. Veja o comando abaixo:

SELECT
produto_id,
to_char (data_vencimento, 'dd/mm/yyyy') AS data_vencimento,
to_char (data_vencimento - interval '3 days', 'dd/mm/yyyy') AS data_recolhimento
FROM tb_produtos;

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.



2º Exemplo

Uma imobiliária deve oferecer aos seus locatários uma renovação de contrato três meses antes do término do contrato. Para calcular a data de oferta de renovação, vamos utilizar a tabela "tb_locacao".

tb_locacao

Descrição das colunas
  • locatario_id: identificação do locatário;
  • imovel_ididentificação do imóvel;
  • data_fim_contrato: data do fim do contrato;  
Solução

Podemos subtrair um intervalo de uma data.

 data - inverval 'n período'

Onde:
  • inverval: tipo de dado que armazena um intervalo de tempo no PostgreSql;
  • n: valor numérico
  • período:  armazena a unidade de medida de tempo. Neste caso, vamos utilizar a unidade de tempo months, pois precisamos subtrair meses da coluna data_fim_contrato;

Para exibir as datas de oferta de renovação executamos a consulta abaixo:

SELECT
locatario_id,
imovel_id,
data_fim_contrato,
data_fim_contrato - interval '3 months' AS oferta_renovacao
FROM tb_locacao;

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.



Caso haja necessidade, formate as datas através do comando to_char. Veja o comando abaixo:

SELECT
locatario_id,
imovel_id,
to_char (data_fim_contrato, 'dd/mm/yyyy'),
to_char (data_fim_contrato - interval '3 months', 'dd/mm/yyyy') AS oferta_renovacao
FROM tb_locacao;

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.



3º Exemplo

Uma academia quer consultar quantos alunos se matricularam nos últimos 7 dias. O dia que executei esta consulta era 01/04/2014, ou seja iremos fazer a contagem dos alunos que se matricularam do dia 26/03/2014 até o dia 01/04/2014.
Para verificar a quantidade de alunos matriculados vamos utilizar a tabela "tb_matriculas".

tb_matriculas

Descrição das colunas
  • matricula_id: identificação da matrícula;
  • data_matricula: data da matrícula;
Solução

Para saber a quantidade de alunos matriculados nos últimos 7 dias devemos subtrair 7 dias do dia atual (hoje)
Para retornar o dia de hoje utilizamos a função CURRENT_DATE.


Agora que já sabemos retornar o dia de hoje, vamos subtrair 7 dias da data de hoje.

 CURRENT - inverval 'n período'

Onde:
  • inverval: tipo de dado que armazena um intervalo de tempo no PostgreSql;
  • n: valor numérico;
  • período: armazena a unidade de medida de tempo. Neste caso, vamos utilizar a unidade de tempo days, pois precisamos subtrair dias da data de hoje;
Além disso vamos utilizar a função "COUNT" para fazer a contagem dos alunos.

Para exibir a quantidade de alunos matriculado nos últimos 7 dias executamos a consulta abaixo:

SELECT
COUNT(matricula_id) AS quantidade_alunos
FROM tb_matriculas
WHERE data_matricula > CURRENT_DATE - interval 'days';

Após a execução da sentença, será exibido o resultado. Veja na imagem a seguir.


4º Exemplo

Uma imobiliária deve oferecer aos seus locatários uma renovação de contrato, em um intervalo "n" antes do término do contrato, o intervalo varia de acordo com o tipo de contrato.

Para calcular a data de oferta de renovação, vamos utilizar a tabela "tb_loc_imoveis". Veja a imagem a seguir:


Segue o script de criação da tabela.

/*Cria a tabela "tb_loc_imoveis"*/
CREATE TABLE tb_loc_imoveis
(
loc_id integer,
tipo_id_loc integer,
periodo_renovacao interval,
data_fim_contrato date
);

/*Inserir registros na tabela "tb_loc_imoveis"*/
INSERT INTO tb_loc_imoveis (loc_id, tipo_id_loc, periodo_renovacao, data_fim_contrato)
VALUES
(1221, 1, '1 month 5 days 3 hours 23 minutes', '2021-03-01'),
(1421, 2, '1 year 1 month',  '2021-04-05'),
(1423, 3, '15 days 1 hour',  '2021-05-15'),
(1221, 1, '1 month 5 days 3 hours 23 minutes',  '2021-08-12'),
(1421, 2, '1 year 1 month', '2021-09-17'),
(1423, 3, '15 days 1 hour',  '2021-10-20');


Para calcular as datas de oferta de renovação executamos a consulta abaixo:

SELECT
loc_id,
tipo_id_loc,
periodo_renovacao,
data_fim_contrato,
data_fim_contrato - periodo_renovacao AS oferta_renovacao
FROM tb_loc_imoveis;

Após a execução da consulta, será exibido o resultado na coluna com o alias "oferta_renovacao". Veja na imagem a seguir.





Deixe o seu comentário. Suas sugestões ou críticas serão bem-vindas.

2 comentários:

  1. Que subtrair não um intervalo fixo como no exemplo:
    data_fim_contrato - interval '3 months'

    Mas sim atrves de outro campo na tabela, exemplo:
    CAMPO1 - interval 'CAMPO2 months'

    Tem uma indicação de como fazer?

    ResponderExcluir
  2. Boa tarde, obrigada por sua pergunta. Desculpa a demora em responder.
    Segue um exemplo de como subtrair um intervalo de uma data utilizando uma coluna com o "tipo de dado interval".

    /*Comando para criar a tabela "tb_cadastro"*/
    CREATE TABLE tb_cadastro
    (
    id serial,
    data_referencia timestamp,
    periodo interval,
    CONSTRAINT pk_cad_id PRIMARY KEY(id)
    );

    /*Comando para inserir múltiplos registros na tabela "tb_cadastro".
    */

    INSERT INTO tb_cadastro(data_referencia, periodo)
    VALUES
    ('2020-03-17 13:24:12', '2 years'),
    ('2020-03-17 19:30:15', '2 months'),
    ('2020-03-17 23:45:42', '2 days'),
    ('2020-03-17 23:59:30', '2 hours'),
    ('2020-03-17 23:59:30', '2 minutes'),
    ('2020-03-17 23:59:30', '2 seconds'),
    ('2020-03-17 23:59:30', '2 years 2 months 2 days 2 hours 2 minutes 2 seconds');

    /*Ao executar a consulta, veja a coluna com o alias "data_alerta", esta coluna é o resultado da subtração de um intervalo (data_referencia - periodo).
    */
    SELECT
    id,
    data_referencia,
    periodo,
    data_referencia - periodo AS data_alerta
    FROM tb_cadastro;

    ResponderExcluir