Funções de data e hora no PostgreSQL

1. Tipos de Dados de Data e Hora no PostgreSQL

O PostgreSQL oferece tipos de dados especializados para trabalhar com data e hora, cada um com características específicas:

  • DATE: armazena apenas data (ano, mês, dia), sem hora. Ocupa 4 bytes.
  • TIME: armazena apenas hora (horas, minutos, segundos), sem data. Ocupa 8 bytes.
  • TIMESTAMP: armazena data e hora completas, sem informação de fuso horário. Ocupa 8 bytes.
  • TIMESTAMPTZ: armazena data e hora com fuso horário. Internamente, os valores são convertidos para UTC e armazenados como timestamp com timezone. Ocupa 8 bytes.
  • INTERVAL: representa períodos de tempo (dias, horas, minutos, segundos). Ocupa 16 bytes.

Boas práticas:
- Use TIMESTAMPTZ quando precisar lidar com múltiplos fusos horários
- Use DATE para dados que não exigem horário, como datas de nascimento
- Use INTERVAL para armazenar durações ou prazos

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    nome_evento VARCHAR(100),
    data_inicio DATE,
    hora_evento TIME,
    timestamp_utc TIMESTAMPTZ,
    duracao INTERVAL
);

INSERT INTO eventos VALUES 
(1, 'Conferência', '2024-03-15', '14:30:00', '2024-03-15 17:30:00-03', '3 horas');

2. Obtendo a Data e Hora Atuais

O PostgreSQL oferece diversas funções para capturar o momento atual:

-- Data atual
SELECT CURRENT_DATE;  -- 2024-03-15

-- Hora atual com fuso horário
SELECT CURRENT_TIME;  -- 14:30:00.123-03

-- Timestamp completo com fuso horário
SELECT CURRENT_TIMESTAMP;  -- 2024-03-15 14:30:00.123-03

-- Equivalente ao CURRENT_TIMESTAMP
SELECT NOW();  -- 2024-03-15 14:30:00.123-03

-- Hora local sem fuso horário
SELECT LOCALTIME;  -- 14:30:00.123

-- Timestamp local sem fuso horário
SELECT LOCALTIMESTAMP;  -- 2024-03-15 14:30:00.123

Diferenças de precisão:
- STATEMENT_TIMESTAMP(): retorna o timestamp do início da instrução SQL atual
- CLOCK_TIMESTAMP(): retorna o timestamp real do sistema, mesmo dentro de uma transação

SELECT 
    NOW() AS inicio_transacao,
    CLOCK_TIMESTAMP() AS tempo_real,
    STATEMENT_TIMESTAMP() AS inicio_instrucao;

3. Extração de Componentes de Data e Hora

EXTRACT()

Extrai campos específicos de uma data/hora:

SELECT 
    EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS ano,
    EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS mes,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS dia,
    EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hora,
    EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minuto,
    EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS segundo,
    EXTRACT(DOW FROM CURRENT_TIMESTAMP) AS dia_semana,  -- 0=domingo
    EXTRACT(DOY FROM CURRENT_TIMESTAMP) AS dia_ano;

DATE_PART()

Alternativa ao EXTRACT() com sintaxe diferente:

SELECT 
    DATE_PART('year', CURRENT_TIMESTAMP) AS ano,
    DATE_PART('month', CURRENT_TIMESTAMP) AS mes,
    DATE_PART('day', CURRENT_TIMESTAMP) AS dia;

DATE_TRUNC()

Trunca uma data/hora para uma unidade específica:

SELECT 
    DATE_TRUNC('year', CURRENT_TIMESTAMP) AS inicio_ano,
    DATE_TRUNC('month', CURRENT_TIMESTAMP) AS inicio_mes,
    DATE_TRUNC('week', CURRENT_TIMESTAMP) AS inicio_semana,
    DATE_TRUNC('day', CURRENT_TIMESTAMP) AS inicio_dia,
    DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS inicio_hora;

4. Formatação e Conversão de Datas

TO_CHAR()

Formata data/hora como string:

SELECT 
    TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY') AS data_brasil,
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS formato_iso,
    TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD "de" Month "de" YYYY') AS data_extenso;

TO_DATE() e TO_TIMESTAMP()

Convertem strings para tipos data/hora:

SELECT 
    TO_DATE('15/03/2024', 'DD/MM/YYYY') AS data_convertida,
    TO_TIMESTAMP('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_convertido;

Máscaras comuns de formatação:
- YYYY: ano com 4 dígitos
- MM: mês com 2 dígitos (01-12)
- DD: dia com 2 dígitos (01-31)
- HH24: hora no formato 24h (00-23)
- MI: minutos (00-59)
- SS: segundos (00-59)

5. Operações Aritméticas com Datas e Horas

Soma e subtração de INTERVAL

SELECT 
    CURRENT_DATE + INTERVAL '1 day' AS amanha,
    CURRENT_DATE - INTERVAL '7 days' AS semana_passada,
    CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes' AS daqui_a_duas_horas,
    CURRENT_TIMESTAMP + INTERVAL '3 months' AS trimestre_futuro;

Diferença entre datas

SELECT 
    DATE '2024-12-31' - DATE '2024-01-01' AS dias_entre_datas,  -- Resultado: 365
    TIMESTAMP '2024-12-31 23:59:59' - TIMESTAMP '2024-01-01 00:00:00' AS intervalo,
    AGE(TIMESTAMP '2024-12-31', TIMESTAMP '2024-01-01') AS idade_intervalo;

Cálculo de idade com AGE()

SELECT 
    AGE('1990-05-15') AS idade_atual,
    AGE('2024-03-15', '1990-05-15') AS idade_em_data_especifica;

6. Funções Avançadas e Ajustes de Fuso Horário

AT TIME ZONE

Converte entre fusos horários:

SELECT 
    CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS utc,
    CURRENT_TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AS brasil,
    CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS toquio;

Funções MAKE

Constroem datas e horas manualmente:

SELECT 
    MAKE_DATE(2024, 3, 15) AS data_construida,
    MAKE_TIME(14, 30, 0) AS hora_construida,
    MAKE_TIMESTAMP(2024, 3, 15, 14, 30, 0) AS timestamp_construido,
    MAKE_TIMESTAMPTZ(2024, 3, 15, 14, 30, 0, 'America/Sao_Paulo') AS timestamp_tz_construido;

ISFINITE()

Verifica se uma data/hora é finita (não é infinito):

SELECT 
    ISFINITE(CURRENT_TIMESTAMP) AS finito,
    ISFINITE('infinity'::TIMESTAMP) AS infinito;

7. Exemplos Práticos e Consultas Comuns

Filtros por datas

-- Registros dos últimos 30 dias
SELECT * FROM eventos 
WHERE data_inicio >= CURRENT_DATE - INTERVAL '30 days';

-- Registros do mês atual
SELECT * FROM eventos 
WHERE DATE_TRUNC('month', data_inicio) = DATE_TRUNC('month', CURRENT_DATE);

-- Registros do trimestre atual
SELECT * FROM eventos 
WHERE DATE_TRUNC('quarter', data_inicio) = DATE_TRUNC('quarter', CURRENT_DATE);

Agrupamento por períodos

-- Total de eventos por mês
SELECT 
    DATE_TRUNC('month', data_inicio) AS mes,
    COUNT(*) AS total_eventos
FROM eventos
GROUP BY DATE_TRUNC('month', data_inicio)
ORDER BY mes;

-- Total de eventos por semana
SELECT 
    DATE_TRUNC('week', data_inicio) AS inicio_semana,
    COUNT(*) AS total_eventos
FROM eventos
GROUP BY DATE_TRUNC('week', data_inicio)
ORDER BY inicio_semana;

Cálculo de diferenças entre timestamps

-- Tempo decorrido entre início e fim de eventos
SELECT 
    nome_evento,
    data_inicio,
    CURRENT_TIMESTAMP,
    AGE(CURRENT_TIMESTAMP, data_inicio) AS tempo_decorrido,
    EXTRACT(EPOCH FROM AGE(CURRENT_TIMESTAMP, data_inicio)) AS segundos_decorridos
FROM eventos;

-- Dias úteis entre duas datas (exemplo simples)
SELECT 
    CURRENT_DATE AS hoje,
    CURRENT_DATE + INTERVAL '10 days' AS futuro,
    (CURRENT_DATE + INTERVAL '10 days') - CURRENT_DATE AS dias_corridos;

Referências