DATA no PostgreSQL – Brincando com funções

2012-12-20

Após brincar de inserir e exibir datas e de fazer um pouco de aritimética com datas, chegou a hora de trabalhar com problemas mais complexos. Se você tiver alguma sugestão para um 4º ou até 5º post, deixe um comentário aqui.

Nesse ponto, você já deveria se sentir mais à vontade trabalhando com datas no PostgreSQL. Lembre-se sempre de consultar o capítulo 8 – “Data Types” e o capítulo 9 – “Functions and Operators” para conhecer um pouco mais sobre os tipos de dados disponíveis e as funções existentes para lidar com eles. Para hoje vamos fazer uma brincadeira simples com datas: vamos pegar o primeiro e o último dia útil do mês atual. Antes de mais nada, RTFM…. vá dar uma lida no manual para conhecer as funções do Postgres. Quanto mais eu releio a documentação, mais eu aprendo.

Mas vamos lá. Para começar, vamos pegar o Mês atual, com a função CURRENT_DATE que serve para pegar a data atual. Se você quiser utilizar as função semelhante, que retorna um TIMESTAMP ao invés de um DATE, você terá outras preocupações, como saber em que momento exato será considerado como “data atual”. Por enquanto é tudo que precisamos saber.

# SELECT current_date;
    date
------------
 2012-12-19
(1 row)

Bom, e se quisermos saber qual é o primeiro dia do mês atual? OK, podemos marretar que todo mês começa no dia primeiro, mas vamos trabalhar com SQL um pouco e conhecer outra função que serve para “arredondar” datas, o DATE_TRUNC:

# SELECT date_trunc('month',current_date);
       date_trunc
------------------------
 2012-12-01 00:00:00-02
(1 row)

Simples não? Se fossemos arredondar um número, pensaríamos em qual casa decimal (dédimos, centésimos, milésimos) iríamos arredondar, como na função TRUNC. Com datas, podemos definir arredondar no ano, mês, dia, hora, minuto, segundo, etc.

Mas vamos começar a complicar um pouco… e se nós quisermos pegar o último dia do mẽs? Bom, temos meses com 30, 31, 28 e 29 dias. O postgres já conhece bem o calendário, portanto, o que podemos fazer (sei que tem outra forma de fazer isso) é pegar o primeiro dia do mês seguinte e subtrair um dia. Vejamos alguns exemplos:

# SELECT date_trunc('month',current_date) + INTERVAL'1 month' - INTERVAL'1 day';
        ?column?
------------------------
 2012-12-31 00:00:00-02

# -- Vamos pegar o último dia de novembro
# SELECT date_trunc('month',DATE'2012-11-19') + INTERVAL'1 month' - INTERVAL'1 day';
        ?column?
------------------------
 2012-11-30 00:00:00-02
(1 row)

# -- Vamos pegar o último dia de fevereiro num ano bisexto
# SELECT date_trunc('month',DATE'2012-02-16') + INTERVAL'1 month' - INTERVAL'1 day';
        ?column?
------------------------
 2012-02-29 00:00:00-03
(1 row)

# -- Vamos pegar o último dia de fevereiro num ano não bisexto
# SELECT date_trunc('month',DATE'2011-02-16') + INTERVAL'1 month' - INTERVAL'1 day';
        ?column?
------------------------
 2011-02-28 00:00:00-03
(1 row)

Ótimo, agora temos o primeiro e o último dia do mês atual, correto? Agora vamos gerar uma tabela com todos os dias do mês usando a função GENERATE_SERIES:

=# SELECT *
-# FROM generate_series(DATE'2012-12-01',DATE'2012-12-31',INTERVAL'1 day');
    generate_series
------------------------
 2012-12-01 00:00:00-02
 2012-12-02 00:00:00-02
 2012-12-03 00:00:00-02
 2012-12-04 00:00:00-02
 2012-12-05 00:00:00-02
 2012-12-06 00:00:00-02
 2012-12-07 00:00:00-02
 2012-12-08 00:00:00-02
 2012-12-09 00:00:00-02
 2012-12-10 00:00:00-02
 2012-12-11 00:00:00-02
 2012-12-12 00:00:00-02
 2012-12-13 00:00:00-02
 2012-12-14 00:00:00-02
 2012-12-15 00:00:00-02
 2012-12-16 00:00:00-02
 2012-12-17 00:00:00-02
 2012-12-18 00:00:00-02
 2012-12-19 00:00:00-02
 2012-12-20 00:00:00-02
 2012-12-21 00:00:00-02
 2012-12-22 00:00:00-02
 2012-12-23 00:00:00-02
 2012-12-24 00:00:00-02
 2012-12-25 00:00:00-02
 2012-12-26 00:00:00-02
 2012-12-27 00:00:00-02
 2012-12-28 00:00:00-02
 2012-12-29 00:00:00-02
 2012-12-30 00:00:00-02
 2012-12-31 00:00:00-02
(31 rows)

Agora vamos fazer a mesma coisa, mas vamos substituir o primeiro e o último dia do mês pelas expressões que criamos antes:

=# SELECT * FROM generate_series(
-#    date_trunc('month',current_date),
-#    date_trunc('month',current_date) + INTERVAL'1 month' - INTERVAL'1 day',
-#    INTERVAL'1 day');
    generate_series
------------------------
 2012-12-01 00:00:00-02
 2012-12-02 00:00:00-02
 2012-12-03 00:00:00-02
 2012-12-04 00:00:00-02
 2012-12-05 00:00:00-02
 2012-12-06 00:00:00-02
 2012-12-07 00:00:00-02
 2012-12-08 00:00:00-02
 2012-12-09 00:00:00-02
 2012-12-10 00:00:00-02
 2012-12-11 00:00:00-02
 2012-12-12 00:00:00-02
 2012-12-13 00:00:00-02
 2012-12-14 00:00:00-02
 2012-12-15 00:00:00-02
 2012-12-16 00:00:00-02
 2012-12-17 00:00:00-02
 2012-12-18 00:00:00-02
 2012-12-19 00:00:00-02
 2012-12-20 00:00:00-02
 2012-12-21 00:00:00-02
 2012-12-22 00:00:00-02
 2012-12-23 00:00:00-02
 2012-12-24 00:00:00-02
 2012-12-25 00:00:00-02
 2012-12-26 00:00:00-02
 2012-12-27 00:00:00-02
 2012-12-28 00:00:00-02
 2012-12-29 00:00:00-02
 2012-12-30 00:00:00-02
 2012-12-31 00:00:00-02
(31 rows)

Pronto… estamos quase lá agora. Vamos pegar apenas o primeiro e o último dia usando as funções de agregação MIN e MAX:

=# SELECT min(dias), max(dias)
-# FROM generate_series(date_trunc('month',current_date),date_trunc('month',current_date) + INTERVAL'1 month' - INTERVAL'1 day',INTERVAL'1 day') AS dias;
          min           |          max
------------------------+------------------------
 2012-12-01 00:00:00-02 | 2012-12-31 00:00:00-02
(1 row)

Sim, parece que estamos dando voltas, não? Mas veja, para pegar o primeiro e o último dia do ano, só falta para nós filtrar o sábado e o domingo. E isso é fácil se utilizarmos a função EXTRACT (similar a função DATE_PART) que extrai apenas uma parte de uma data. No caso, queremos saber de qual dia da semana ela é:

=# SELECT
-#     EXTRACT(DAY FROM min(dias)) AS primeiro_dia_util,
-#     EXTRACT(DAY FROM max(dias)) AS ultimo_dia_util
-# FROM generate_series(
(#     date_trunc('month',current_date),
(#     date_trunc('month',current_date) + INTERVAL'1 month' - INTERVAL'1 day',
(#     INTERVAL'1 day') AS dias
-# WHERE EXTRACT(ISODOW FROM dias) < 6;
 primeiro_dia_util | ultimo_dia_util
-------------------+-----------------
                 3 |              31
(1 row)

Aqui utilizei o ISODOW que é o dia da semana, onde a segunda-feira é o dia 1 e o domingo é o dia 7, conforme o padrão ISO. Também aproveitei para extrair apenas o dia do mês na hora de exibir a informação, o que é absolutamente opcional. Você pode testar esta consulta para outros intervalos, substituindo o CURRENT_DATE por outras datas.

Como sugestão para uma aplicação real você pode criar uma tabela de feriados e filtrar as datas dos feriados também:

CREATE TABLE feriados (data date, descr text);
INSERT INTO feriados VALUES ('2012-12-25','Natal');
INSERT INTO feriados VALUES ('2013-01-01','Confraternização Universal');
INSERT INTO feriados VALUES ('2012-11-15','Proclamação da República');
INSERT INTO feriados VALUES ('2012-11-02','Finados');

SELECT
    EXTRACT(DAY FROM min(dias)) AS primeiro_dia_util,
    EXTRACT(DAY FROM max(dias)) AS ultimo_dia_util
FROM generate_series(
    date_trunc('month',current_date),
    date_trunc('month',current_date) + INTERVAL'1 month' - INTERVAL'1 day',
    INTERVAL'1 day') AS dias
WHERE
    EXTRACT(ISODOW FROM dias) < 6 AND
    NOT EXISTS (SELECT data FROM feriados WHERE dias = data);

Você sofisticar mais o problema criando uma tabela que diferencie com feriados nacionais, estaduais e municipais. Mas isso fica para você pensar, se realmente tiver que lidar com este tipo de problema.

PostgreSQL

Comments

24 respostas para “DATA no PostgreSQL – Brincando com funções”

Eduardo Royer

2014-09-15

Porra, show de bola em cara. Estou inciando em funções plpgsql e cara, tu me ajudou bastante. Já adicionei aos favoritos 😉

Parabéns pelo site! Abraços.

Responder
Luis Romano

2015-02-23

Muito bom seu post, mas eu tenho uma dúvida. Como pegar todos os registros de uma tabela do mês passado sem informar a data?

Responder
1. telles
  
  2015-02-24
  
  Luis, tente algo como:
  
  SELECT * FROM tabela_com_data
  WHERE campo_data BETWEEN
  date_trunc(‘month’,current_date) – INTERVAL’1 month’ AND
  date_trunc(‘month’,current_date)
  
  Responder
  1. Luis Romano
    
    2015-02-24
    
    Telles, muito obrigado por ter respondido, a alguns minutos essa minha dúvida também foi respondia através de um fórum, mas foi exatamente a mesma solução.
    
    Muito obrigado novamente.
    
    Responder
littiele Oliveira

2015-12-17

Ola.

eu tenho um banco no PostgreSQL o o nome da tabela clientes e a coluna chamada nasc.

Gostaria de saber como faço para pegar por exemplo todos os registros com o mes 4. ja tentei de todas as formas e nao consigo.
No SQL SERVER eu consegui assim :

SELECT nasc FROM clientes WHERE month(nasc)= ’12’ ;

porem no PostgreSQL ele não aceita esse comando, da o seguinte erro:

ERRO: função month(character varying) não existe
LINE 1: SELECT nasc FROM clientes WHERE MONTH(nasc)= ’04’
^
HINT: Nenhuma função corresponde com o nome e os tipos de argumentos informados.

Responder
telles

2015-12-17

Veja, a função ISO é a date_part, veja na documentação em: http://www.postgresql.org/docs/current/static/functions-datetime.html . Segue um exemplo:

postgres=# select current_date, date_part('month',current_date); date | date_part ------------+----------- 2015-12-17 | 12

Responder
1. littiele Oliveira
  
  2015-12-17
  
  Bom dia Telles,
  
  Poderia me da mais uma forcinha eu entendi o que você passou porem so nao estou conseguindo aplicar isso agora no banco de dados conforme abaixo pode me ajudar ?
  
  select nasc from clientes, date_part(‘month’,nasc)=’04’
  
  Responder
  1. telles
    
    2015-12-17
    
    Veja que a função date_part não retorna um VARCHAR. Veja o exemplo:
    
    postgres=# select current_date, date_part(‘month’,current_date), date_part(‘month’,current_date) = 2 test;
    date | date_part | test
    ————+———–+——
    2015-12-17 | 12 | f
    (1 row)
    
    postgres=# select current_date, date_part(‘month’,current_date), date_part(‘month’,current_date) = 12 test;
    date | date_part | test
    ————+———–+——
    2015-12-17 | 12 | t
    (1 row)
    
    Responder
    1. littiele Oliveira
      
      2015-12-17
      
      Entendi… ele so vai me dizer se é verdadeiro ou falso….
      
      Porque so pra vc entender eu tenho um sistema onde quando o cliente digitar por exemplo o mes 04 ele vai listar todos os que são do mes 04 e gerando true ou false ele nao vai mostrar depois num tabela quem são essas pessoas.
      
      Responder
      1. telles
        
        2015-12-17
        
        Meu caro, você precisa olhar fora da caixa… toda condição no WHERE é uma checagem de verdadeiro ou falso. Eu só coloquei no SELECT para você ver o resultado. Dê uma olhada na documentação….
        
        O que você quer é algo realmente simples e rápido:
        
        SELECT nasc FROM clientes WHERE MONTH(nasc)= 04;
        
        É só retirar as aspas simples. Entendeu?
      2. littiele Oliveira
        
        2015-12-17
        
        entendi…vlw
rafael

2015-12-29

Cara, tu manda aí as postagens sobre situações, e quando mostra como se faz, parece que é tudo fácil.
Muito bom, parabéns, show demais.
Adicionado como favorito!

Responder
Fred Henrique

2016-03-01

Show seu post Telles,

me tira uma dúvida, eu preciso que na minha consulta SELECT em um banco , o campo data de minha tabela “2013-07-18 12:56:19.553” apareça sem os milessegundos, dessa forma “2013-07-18 12:56:19”. Qual seria a melhor forma?

Responder
1. telles
  
  2016-03-04
  
  A solução está em formatar a saída, usando o to_char. Ex: SELECT to_char(campo_timestamp,’YYYY-MM-DD HH24:MI:SS.MS’) FROM …
  
  Vide documentação: http://www.postgresql.org/docs/current/static/functions-formatting.html
  
  Responder
  1. Nilton
    
    2016-03-16
    
    amigo saudações estou com uma pequena duvida como ficaria uma consulta onde a coluna vencimento grava apenas o dia do vencimento “10” a consulta resultaria em todos com 7 ou mais dias de vencidos ?
    desde já agradeço a atenção
    
    Responder
    1. telles
      
      2016-03-16
      
      Vamos supor que a data de vencimento seja hoje. Então você quer pegar datas menores que a data de vencimento – 7 dias, que seria:
      
      teste=# SELECT current_date – ‘7 days’::interval v;
      
      v
      
      2016-03-09 00:00:00
      
      Depois vamos pegar o número do dia e transformar numa data:
      teste=# SELECT * from d;
      
      venc
      
      3
      
      17
      15
      22
      28
      8
      1
      5
      (8 registros)
      
      teste=# SELECT (date_trunc(‘month’,current_date) + venc * ‘1 day’::interval)::date from d;
      
      date
      
      2016-03-04
      2016-03-18
      2016-03-16
      2016-03-23
      2016-03-29
      2016-03-09
      2016-03-02
      2016-03-06
      (8 registros)
      
      Agora é só juntar tudo:
      teste=# SELECT (date_trunc(‘month’,current_date) + venc * ‘1 day’::interval)::date
      FROM d
      WHERE (date_trunc(‘month’,current_date) + venc * ‘1 day’::interval)::date < current_date – '7 days'::interval;
      
      date
      
      2016-03-04
      2016-03-02
      2016-03-06
      (3 registros)
      
      Espero ter ajudado
      
      Responder
adalto.silva

2017-07-13

muito bom, ajudou muito. grato meu amigo Telles.

Responder
Arthur Lehdermann

2017-09-19

Republicou isso em Arthur Lehdermanne comentado:
Agradecimento ao autor original: savepoint.blog.br

Responder
marcus

2017-11-30

Muito bom o Post, mas tenho uma duvida estou com dificuldades para criar uma função de SLA.
so que tenho que tirar os feriados e os dias uteis eu tenho todos os dados, tentei usar uma função passada por você em outro post mas não consegui nenhum resultado.

Responder
Kellyton

2020-01-30

Telles, bom dia
Amigo, como faço para selecionar os dados (datas) de uma tabela e exibir o resultado, mesmo que o dia não esteja nesses dados. Ex. Estou filtrando os dados de uma tabela x, entre datas, mas só aparecem duas datas e eu quero que apareça o intervalo, mesmo que seja Null na tabela de origem.
Ex. que uso
Select * from tbl_origem where data between (’01/01/2020′ and ’31/01/2020);
o problema é que aparecem apenas duas datas
24/01/2020 e 25/01/2020, mas quero que apareça todos o intervalo que foi pré-selecionado no filtro.

Responder
1. Fábio Telles Rodriguez
  
  2020-03-21
  
  SELECT *.t, d
  FROM
  tbl_origem t
  RIGHT JOIN generate_series(‘2020-01-01′,’2020-01-31′,’1 day’)
  WHERE data BETWEEN ‘2020-01-01’ AND ‘2020-01-31’
  ;
  
  Responder
Douglas

2022-11-03

Boa tarde Fábio
Tenho a seguinte situação:
Preciso retornar os dados de uma consulta desde o dia 01/01 do ano anterior. Ou seja, hoje preciso dos dados desde 01/01/2021. Ano que vem serão os dados desde 01/01/2022 e assim por diante.
Hoje retorna apenas dos últimos 365 dias:
select *
from pedidosfaturados P
where DataEmissao > CURRENT_DATE -365

Consegue me ajudar?

Responder
1. Fábio Telles Rodriguez
  
  2022-11-03
  
  Olha, esse é tranquilo. Vamos lá:
  
  SELECT * FROM pedidosfaturados p
  WHERE p.dataemissao > date_trunc(‘year’, current_date) – INTERVAL ‘1 YEAR’;
  
  Veja se funciona aí!
  
  Responder
Fábio Telles Rodriguez

2022-11-03

Olha, esse é tranquilo. Vamos lá:

SELECT * FROM pedidosfaturados p
WHERE p.dataemissao > date_trunc(‘year’, current_date) – INTERVAL ‘1 YEAR’;

Veja se funciona aí!

Responder