Funções de data do PostgreSQL

Compreender as funções de data e hora em seu banco de dados é essencial para uma análise de dados e relatórios eficazes. Continue lendo para saber mais sobre as funções de data do PostgreSQL.

Este artigo aborda algumas das funções de data e hora mais úteis do PostgreSQL e suas aplicações em análises de dados e relatórios. As funções de data SQL facilitam diferentes tarefas de análise de dados, incluindo análise de vendas, relatórios financeiros, análise de websites e muito mais.

Este artigo apresenta a você as ferramentas, na forma de funções de data e hora do PostgreSQL, usadas para realizar essas tarefas. Começaremos com uma rápida visão geral dos tipos de dados de data e hora disponíveis no PostgreSQL antes de passar para as funções.

Dê uma olhada em nosso SQL Cookbook, que contém uma variedade de receitas SQL para o PostgreSQL e outros dialetos. É um recurso valioso que vale a pena marcar nos favoritos para referência rápida ao escrever suas consultas. Você pode saber mais sobre o conceito de um livro de receitas SQL aqui.

Tipos de dados de data e hora no PostgreSQL

No PostgreSQL, há vários tipos de dados disponíveis para armazenar valores de data e hora. Abaixo está uma visão geral dos tipos usados com mais frequência:

Para análises de dados que precisam levar em consideração diferentes fusos horários, o PostgreSQL oferece tipos de dados como TIME WITH TIME ZONE (que armazena valores de hora com informações de fuso horário) e TIMESTAMP WITH TIME ZONE (que armazena valores de data e hora com informações de fuso horário).

Leia nosso artigo Uma visão geral dos tipos de dados do PostgreSQL para saber mais sobre os tipos de dados disponíveis no PostgreSQL. E se você quiser praticar mais, confira estes 19 exercícios no PostgreSQL com soluções detalhadas.

Usaremos todos os tipos de dados acima enquanto exploramos as funções de data e hora do PostgreSQL. Vamos começar!

Visão geral das funções de data e hora no PostgreSQL

Todas as funções de data e hora e no PostgreSQL podem ser agrupadas nas seguintes categorias:

• Comparação de valores de data e hora
• Obtenção de valores atuais de data e hora
• Realização de operações aritméticas em valores de data e hora
• Manipulação de valores de data e hora
• Formatação de valores de data e hora

As seções a seguir listam todas as funções relevantes com exemplos.

Esta é a tabela sales tabela que usaremos em nossos exemplos:

Comparação de valores de data e hora

Para comparar valores de data e hora no PostgreSQL, usamos operadores de comparação padrão. Esses operadores incluem =, !=, <>, >, >=, <, <=, BETWEEN, e NOT BETWEEN.

Você pode ver exemplos de uso de cada operador de comparação abaixo.

Operador Equal (=)

Pergunta: Quantas vendas ocorreram em 1º de junho de 2024?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_date = '2024-06-01';

Operador Not Equal (!= ou <>)

Pergunta: Quantas vendas não ocorreram às 10:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_time != '10:00:00';

Operador Maior que (>)

Pergunta: Quantas vendas ocorreram após 1º de junho de 2024, às 10:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp > '2024-06-01 10:00:00';

Observação: ao comparar datas no SQL, o operador > significa que, se a data X for maior que a data Y, a data X está mais distante no futuro (mais tarde) que a data Y.

Operador maior que ou igual (>=)

Pergunta: Quantas vendas ocorreram em ou após as 10:00:00 de 1º de junho de 2024?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp >= '2024-06-01 10:00:00';

Observação: ao comparar carimbos de data/hora no SQL, o operador >= significa que, se o carimbo de data/hora X for maior ou igual ao carimbo de data/hora Y, o carimbo de data/hora X está mais no futuro (mais tarde) do que ou é igual ao carimbo de data/hora Y.

Operador Less Than (<)

Pergunta: Quantas vendas ocorreram antes das 12:00:00 de 1º de junho de 2024?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp < '2024-06-01 12:00:00';

Observação: Ao comparar registros de data e hora no SQL, o operador < significa que, se o registro de data e hora X for menor que o registro de data e hora Y, X é anterior a Y.

Operador Menor que ou Igual (<=)

Pergunta: Quantas vendas ocorreram em ou antes de 1º de junho de 2024, às 12:00:00?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp <= '2024-06-01 12:00:00';

Observação: ao comparar datas ou horários em SQL, o operador <= significa que, se a data X for menor ou igual à data Y, a data X é anterior ou igual à data Y.

Operador BETWEEN

Pergunta: Quantas vendas ocorreram entre 10:00:00 e 14:00:00?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Operador NOT BETWEEN

Pergunta: Quantas vendas não ocorreram entre 10:00:00 e 14:00:00?

Resposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp NOT BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

É bom saber: Você pode classificar a saída por data, hora ou carimbo de data/hora simplesmente incluindo a coluna relevante na cláusula ORDER BY.

Consulte estes livros de receitas para saber mais:

• Como comparar duas datas na cláusula WHERE no SQL
• Como comparar valores de data e hora no SQL
• Como comparar valores de data no SQL
• Como ordenar por data no PostgreSQL ou Oracle

Obtenção de valores atuais de data e hora

Ao analisar dados, geralmente queremos compará-los com o registro de data e hora atual sempre que a consulta é executada. No PostgreSQL, isso pode ser feito com a função NOW() e as palavras-chave CURRENT_DATE, CURRENT_TIME e CURRENT_TIMESTAMP.

Importante: a função NOW() é específica do PostgreSQL, enquanto as funções CURRENT_* são fornecidas no SQL padrão e são recomendadas para uso em vez da função NOW().

NOW()

Use essa função para selecionar a data e a hora atuais juntamente com o fuso horário.

SELECT NOW();

CURRENT_DATE

Use essa palavra-chave para selecionar a data atual.

SELECT CURRENT_DATE;

CURRENT_TIME

Use essa palavra-chave para selecionar a hora atual junto com o fuso horário (mas sem a data).

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP

Use essa função para selecionar a data e a hora atuais, juntamente com o fuso horário.

SELECT CURRENT_TIMESTAMP;

Confira estes livros de receitas para saber mais:

• Como obter a data atual no PostgreSQL
• Como obter a hora atual no PostgreSQL
• Como obter a data e a hora atuais (sem fuso horário) no PostgreSQL
• Como obter a data e a hora atuais com deslocamento de fuso horário no PostgreSQL
• Como obter a hora atual (sem fuso horário) no PostgreSQL

Operações aritméticas em valores de data e hora

As operações aritméticas em valores de data e hora no PostgreSQL incluem o cálculo da diferença entre duas datas e a adição ou subtração de INTERVALs valores de data/hora.

Siga os exemplos de uso abaixo para:

Calcular a diferença entre dois valores de data usando a função AGE().

SELECT AGE(sale_date, CURRENT_DATE) AS age_difference
FROM sales
WHERE product_name = 'Apples';

Observe que ela retorna o INTERVALO entre dois valores.

Calcular a diferença entre dois valores de data usando o operador menos (-).

SELECT sale_timestamp - CURRENT_TIMESTAMP AS difference
FROM sales
WHERE product_name = 'Apples';

Observe que isso retorna um intervalo entre dois valores, como a função age() faria.

Calcule a diferença entre dois valores de tempo usando o operador menos (-).

SELECT sale_time, sale_time - '09:00:00' AS difference
FROM sales
WHERE product_name = 'Apples';

Adicione INTERVALOS.

Use o operador plus (+) para adicionar um INTERVAL a um valor de data/hora:

SELECT sale_date + INTERVAL '1 day' AS next_day
FROM sales
WHERE product_name = 'Apples';

Subtrair INTERVALOS.

Use o operador de menos (-) para subtrair um INTERVAL de um valor de data/hora.

SELECT sale_date - INTERVAL '1 week' AS previous_week
FROM sales
WHERE product_name = 'Apples';

Confira estes livros de receitas para saber mais:

• Como calcular a diferença de data no PostgreSQL/Oracle
• Como calcular a diferença de carimbo de data/hora no PostgreSQL
• Como encontrar o intervalo entre duas datas no PostgreSQL
• Como obter a data de ontem no PostgreSQL

Manipulação de valores de data e hora

O PostgreSQL fornece funções que facilitam a extração de partes específicas de valores de data/hora, como DATE_PART(), DATE_TRUNC() e EXTRACT(). Na análise de dados, essas funções são comumente utilizadas para agrupar dados por dia, mês ou ano - permitindo, assim, a criação de relatórios com estatísticas sobre períodos de tempo definidos.

DATE_PART()

Extrai um componente específico (por exemplo, ano, mês ou dia) de um valor de data/hora.

SELECT DATE_PART('year', sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

EXTRACT()

Extrai uma parte específica (por exemplo, ano, mês ou dia) de um valor de data/hora.

SELECT EXTRACT(YEAR FROM sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

A função EXTRACT() pode ser usada de forma intercambiável com a função DATE_PART(). Entretanto, EXTRACT() pode lidar com fusos horários.

DATE_TRUNC()

Trunca um valor de data/hora para a unidade especificada (por exemplo, ano, mês ou dia).

SELECT DATE_TRUNC('year', sale_date) AS year_start
FROM sales
WHERE product_name = 'Apples';

MAKE_DATE()

Monta uma data completa a partir de um ano, mês e dia.

SELECT MAKE_DATE('2024', '6', '1') AS date;

GENERATE_SERIES()

Gera uma série de valores de data/hora com intervalos iguais.

SELECT GENERATE_SERIES(
	   '2024-06-01 08:00:00'::timestamp, 
	   '2024-06-01 09:00:00'::timestamp, 
	   '20 minutes'::interval
             ) AS generated_timestamp;

Confira estes livros de receitas para saber mais:

• Como agrupar por mês no PostgreSQL
• Como agrupar por ano no SQL
• Como extrair o número da semana de uma data no PostgreSQL
• Como obter nomes de dias no PostgreSQL
• Como obter o dia do ano a partir de uma data no PostgreSQL
• Como ordenar por nome de mês no PostgreSQL ou Oracle
• Como obter o mês anterior no SQL

Formatação de valores de data e hora

O PostgreSQL oferece várias funções de formatação que facilitam a leitura de seus relatórios. Elas incluem TO_CHAR(), TO_DATE() e TO_TIMESTAMP().

TO_CHAR()

Converte um valor de data/hora em uma string formatada de acordo com um formato especificado.

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

Confira os formatos de data/hora disponíveis fornecidos pelo PostgreSQL aqui.

TO_DATE()

Converte uma string em um valor de data de acordo com um formato especificado.

SELECT TO_DATE('2024-06-01', 'YYYY-MM-DD') AS date_value;

TO_TIMESTAMP()

Converte uma string em um valor de carimbo de data/hora de acordo com um formato especificado.

SELECT TO_TIMESTAMP('2024-06-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS timestamp_value;

Confira estes livros de receitas para saber mais:

• Como formatar uma data no PostgreSQL
• Como converter uma string em uma data no PostgreSQL
• Como converter uma string em um carimbo de data/hora no PostgreSQL

Trabalhando com as funções de data e hora do PostgreSQL

As funções de data e hora, conforme oferecidas pelo PostgreSQL, são ferramentas cruciais na análise de dados e na geração de relatórios. Elas podem nos ajudar a entender padrões de dados ao longo do tempo, como tendências de vendas ou picos de tráfego no site. Com essas funções, podemos agrupar os dados por dia, mês ou ano, facilitando a identificação de tendências e a tomada de decisões informadas. Além disso, elas nos ajudam a formatar nossos relatórios, tornando-os mais fáceis de ler e entender.

Como os dados geralmente são armazenados em diferentes bancos de dados ou arquivos, você pode importar seus dados no formato CSV para o PostgreSQL para processamento posterior.

Se você precisar de um lembrete prático sobre as funções SQL, confira nossa folha de dicasFunções Comuns em SQL gratuita. Você pode baixá-la, imprimi-la e mantê-la à mão enquanto trabalha e aprende.

Se quiser solidificar seus conhecimentos sobre o PostgreSQL, confira nosso curso abrangente SQL de A a Z no PostgreSQL. Ele oferece centenas de exercícios para ensinar tudo o que você precisa saber para se tornar um profissional do Postgres, inclusive conceitos avançados como funções de janela, consultas recursivas e PostGIS. Bom aprendizado!