Operações de Data e Hora no PostgreSQL

Introdução

Neste laboratório, você explorará as poderosas operações de data e hora do PostgreSQL. Você aprenderá a trabalhar com timestamps que incluem informações de fuso horário, uma habilidade crucial para aplicações que lidam com dados de diferentes localizações geográficas.

Você começará criando uma tabela projetada para armazenar dados de eventos, utilizando o tipo de dado TIMESTAMPTZ para garantir a consciência do fuso horário. Em seguida, você inserirá dados para eventos que ocorrem em diferentes fusos horários e aprenderá como o PostgreSQL normaliza esses dados. Finalmente, você praticará a consulta e manipulação desses dados temporais usando funções como EXTRACT, a cláusula AT TIME ZONE e o cálculo de diferenças de tempo com INTERVAL.

Criar uma Tabela para Eventos Baseados em Tempo

Nesta etapa, você se conectará ao banco de dados PostgreSQL e criará uma tabela para armazenar informações de eventos. Esta tabela usará o tipo de dado TIMESTAMPTZ, que é essencial para armazenar com precisão timestamps de vários fusos horários.

Compreendendo TIMESTAMPTZ

O tipo de dado TIMESTAMPTZ (timestamp com fuso horário) no PostgreSQL foi projetado para lidar com datas e horas de todo o mundo. Ao inserir um valor com um fuso horário específico, o PostgreSQL o converte para o Tempo Universal Coordenado (UTC) para armazenamento. Essa padronização facilita a comparação e o cálculo de horários, independentemente de seu fuso horário original.

Etapa 1: Conectar ao PostgreSQL

Primeiro, abra um terminal. Conecte-se ao banco de dados PostgreSQL usando o shell interativo psql. Você executará todos os comandos SQL subsequentes neste shell.

sudo -u postgres psql

Agora você verá o prompt do PostgreSQL, que se parece com postgres=#.

Etapa 2: Criar a Tabela events

Agora, execute o seguinte comando SQL para criar a tabela events. Esta tabela armazenará um ID, um nome e um horário para cada evento.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

Este comando cria uma tabela chamada events com três colunas:

• id: Uma chave primária auto-incrementável.
• event_name: Um campo de texto para o nome do evento.
• event_time: Um timestamp com suporte a fuso horário.

Etapa 3: Verificar a Criação da Tabela

Para confirmar que a tabela foi criada corretamente, você pode inspecionar sua estrutura com o comando \d.

\d events

Você deverá ver o esquema da tabela events, confirmando suas colunas e tipos de dados.

                                     Table "public.events"
   Column   |          Type          | Collation | Nullable |              Default
------------+------------------------+-----------+----------+-----------------------------------
 id         | integer                |           | not null | nextval('events_id_seq'::regclass)
 event_name | character varying(255) |           |          |
 event_time | timestamp with time zone |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)

Você configurou com sucesso a tabela para armazenar dados baseados em tempo.

Inserir Dados com Informações de Fuso Horário

Nesta etapa, você inserirá registros na tabela events. Cada registro terá um timestamp associado a um fuso horário específico, demonstrando como o PostgreSQL lida com dados de diferentes localizações geográficas.

Etapa 1: Inserir Dados de Eventos

Enquanto estiver no shell psql, execute as seguintes instruções INSERT para adicionar três eventos à sua tabela. Observe como cada event_time inclui um identificador de fuso horário.

Insira uma conferência de Los Angeles (UTC-8):

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');

Insira uma reunião de projeto de Londres (UTC+0):

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');

Insira uma reunião de acompanhamento, também de Los Angeles:

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');

Etapa 2: Verificar a Inserção de Dados

Para ver como o PostgreSQL armazenou esses dados, consulte a tabela events.

SELECT * FROM events;

Você deverá ver a seguinte saída.

 id |    event_name     |        event_time
----+-------------------+------------------------
  1 | Conference Call   | 2024-01-20 18:00:00+00
  2 | Project Meeting   | 2024-01-20 18:00:00+00
  3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)

Observe que todos os valores de event_time são exibidos em UTC (indicado pelo deslocamento +00). Por exemplo, 10:00:00 em Los Angeles (UTC-8) foi convertido para 18:00:00 UTC. Essa conversão interna é o que permite comparações consistentes baseadas em tempo.

Consultar e Formatar Timestamps

Agora que você tem dados armazenados em UTC, pode consultá-los e exibi-los em qualquer fuso horário que precisar. Nesta etapa, você usará a função EXTRACT e a cláusula AT TIME ZONE para manipular e formatar seus timestamps.

Etapa 1: Extrair Partes de um Timestamp com EXTRACT

A função EXTRACT permite extrair componentes específicos (como ano, mês, hora) de um timestamp. Vamos obter o ano de cada evento.

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

A consulta extrai o ano do timestamp UTC armazenado.

    event_name     | event_year
-------------------+------------
 Conference Call   |       2024
 Project Meeting   |       2024
 Follow-up Meeting |       2024
(3 rows)

Etapa 2: Converter Timestamps com AT TIME ZONE

Para visualizar o horário UTC armazenado em um fuso horário local específico, use a cláusula AT TIME ZONE. Vamos converter todos os horários de eventos de volta para o fuso horário America/Los_Angeles.

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

Esta consulta pega o event_time UTC e exibe seu horário local equivalente em Los Angeles.

    event_name     |   los_angeles_time
-------------------+---------------------
 Conference Call   | 2024-01-20 10:00:00
 Project Meeting   | 2024-01-20 10:00:00
 Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)

Como você pode ver, a 'Project Meeting', que ocorreu às 18:00:00 UTC, é corretamente exibida como 10:00:00 no horário de Los Angeles.

Etapa 3: Combinar EXTRACT e AT TIME ZONE

Você pode combinar esses recursos para consultas mais poderosas. Por exemplo, para encontrar a hora de cada evento como ocorreu no fuso horário Europe/London:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;

Esta consulta primeiro converte o horário para o fuso horário de Londres e, em seguida, extrai a hora.

    event_name     | event_hour_london
-------------------+-------------------
 Conference Call   |                18
 Project Meeting   |                18
 Follow-up Meeting |                20
(3 rows)

Calcular Diferenças de Tempo

Um requisito comum é calcular a duração entre dois eventos. No PostgreSQL, subtrair um timestamp de outro resulta em um tipo de dado INTERVAL, que representa uma duração de tempo.

Etapa 1: Calcular a Diferença Entre Dois Eventos

Vamos encontrar o tempo decorrido entre a 'Conference Call' e a 'Follow-up Meeting'. Podemos conseguir isso unindo a tabela events a si mesma.

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    e2.event_time - e1.event_time AS time_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Esta consulta funciona da seguinte forma:

• Cria duas instâncias virtuais da tabela events, e1 e e2.
• Filtra e1 para conter apenas a linha 'Conference Call'.
• Filtra e2 para conter apenas a linha 'Follow-up Meeting'.
• Subtrai o event_time de e1 do de e2.

O resultado é um valor do tipo INTERVAL.

     event1      |      event2       | time_difference
-----------------+-------------------+-----------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

A diferença de tempo é de 1 dia e 2 horas.

Etapa 2: Extrair Componentes de um INTERVAL

Você também pode usar EXTRACT em um INTERVAL para obter partes específicas da duração, como o número de dias ou horas. Vamos extrair apenas o número de dias do cálculo anterior.

SELECT
    EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Isso lhe dará um valor numérico para os dias.

 days_difference
-----------------
               1
(1 row)

Etapa 3: Sair do PostgreSQL

Você completou os exercícios para este laboratório. Agora você pode sair do shell psql e retornar ao terminal principal.

\q

Resumo

Neste laboratório, você explorou operações essenciais de data e hora no PostgreSQL. Você aprendeu a:

• Criar uma tabela usando o tipo de dado TIMESTAMPTZ para armazenar corretamente informações cientes de fuso horário.
• Inserir timestamps com fusos horários especificados e observar como o PostgreSQL os converte para UTC para armazenamento.
• Usar a função EXTRACT para recuperar componentes específicos como o ano ou a hora de um timestamp.
• Usar a cláusula AT TIME ZONE para exibir horários UTC armazenados em qualquer fuso horário local desejado.
• Calcular a duração entre dois eventos, que resulta em um INTERVAL, e extrair componentes desse intervalo.

Essas habilidades são fundamentais para construir aplicações robustas que gerenciam dados temporais com precisão em diferentes regiões.