TO_TIMESTAMP no SQL: parsear strings e montar timestamps a partir do epoch Unix

O TO_TIMESTAMP do PostgreSQL faz dois trabalhos bem diferentes sob o mesmo nome: parsear uma string segundo um modelo de formato e montar um momento no tempo a partir de um numero de segundos do epoch Unix. As duas formas retornam timestamptz, e esse unico fato e a origem de quase toda a confusao com fusos horarios em volta da funcao.

Parsear uma string por modelo

O primeiro modo recebe uma string e um modelo de formato. O modelo diz como ler a entrada: YYYY e o ano, MM o mes, DD o dia, HH24 o relogio de 24 horas, MI os minutos, SS os segundos.

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS ts;
-- 2024-03-15 14:30:00+00 (with server timezone UTC)

O uso classico e carregar dados sujos, onde a data chega como string num formato fora do padrao:

INSERT INTO users (id, email, name, country, created_at)
VALUES (1, 'kate@example.com', 'Kate', 'DE',
        TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI'));

Propriedades praticas do modo modelo:

• TO_TIMESTAMP e mais tolerante que um cast ::timestamptz: costuma aceitar espacos sobrando.
• O resultado e sempre timestamptz, ancorado ao TimeZone atual da sessao.
• Para uma data sem hora existe o irmao TO_DATE, com a mesma linguagem de modelos.

Montar tempo a partir do epoch Unix

O segundo modo recebe um unico numero: os segundos decorridos desde 1970-01-01 00:00:00 UTC. A parte fracionaria fornece precisao abaixo do segundo.

SELECT TO_TIMESTAMP(1710513000)      AS from_epoch,
       TO_TIMESTAMP(1710513000.5)    AS with_millis;
-- 2024-03-15 14:30:00+00 | 2024-03-15 14:30:00.5+00

E uma mao na roda quando o tempo e guardado como bigint (o formato comum de logs e eventos do backend):

SELECT id, user_id, amount,
       TO_TIMESTAMP(created_at) AS created_ts
FROM orders
WHERE created_at >= 1704067200;  -- since 2024-01-01 UTC

O ponto-chave: o numero e sempre interpretado como segundos UTC. O instante em si e absoluto; so a sua representacao textual depende do TimeZone da sessao.

Fusos horarios: a grande pegadinha

As duas formas retornam timestamptz. Isso significa que internamente fica guardado um instante UTC absoluto, exibido no fuso da sua sessao na saida. A mesma consulta da um texto diferente com outro SET TIME ZONE.

SET TIME ZONE 'UTC';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00+00

SET TIME ZONE 'America/Sao_Paulo';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00-03

Pegadinha: ao parsear uma string o modelo nao carrega fuso, entao os digitos de ano-mes-dia-hora sao lidos como hora local da sessao, e nao como UTC. Se a string esta de fato em UTC, alinhe de forma explicita:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
         AT TIME ZONE 'UTC' AS naive_utc;

Volta com EXTRACT(EPOCH)

A ida e volta se fecha com EXTRACT(EPOCH FROM ...), que devolve os segundos do epoch Unix de um timestamptz. O par TO_TIMESTAMP e EXTRACT(EPOCH ...) e reversivel.

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-03-15 14:30:00+00') AS epoch;
-- 1710513000

SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM created_at)) = created_at AS same
FROM orders
LIMIT 1;
-- t

Isso e comodo para diferencas e para guardar tempo em APIs numericas. A idade de um pedido em horas e uma subtracao de epochs:

SELECT id,
       (EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM created_at)) / 3600.0
         AS age_hours
FROM orders
WHERE status = 'paid';

Diferencas no MySQL e ClickHouse

O nome TO_TIMESTAMP pertence ao mundo do PostgreSQL e do Oracle; outros motores escrevem de outra forma.

• MySQL parseia uma string com STR_TO_DATE(str, format) usando seus proprios codigos de formato (%Y, %m, %d, %H, %i). Monta um momento a partir do epoch com FROM_UNIXTIME(seconds) e volta com UNIX_TIMESTAMP(ts).

SELECT STR_TO_DATE('2024-03-15 14:30', '%Y-%m-%d %H:%i') AS parsed,
       FROM_UNIXTIME(1710513000)                          AS from_epoch;

• ClickHouse usa parseDateTime (sintaxe parecida com a do MySQL) e fromUnixTimestamp(seconds), com toUnixTimestamp(ts) para o sentido inverso.

SELECT fromUnixTimestamp(1710513000) AS from_epoch,
       toUnixTimestamp(now())        AS to_epoch;

Tenha a separacao em mente: um unico numero de segundos e um instante UTC absoluto, enquanto uma string com modelo sempre arrasta a pergunta de em qual fuso ela esta. Mantenha os dois modos separados na cabeca e o TO_TIMESTAMP para de te surpreender.