TO_DATE no PostgreSQL: converter uma string em data com modelo

Quando uma data chega como string num formato que nao e ISO, um simples cast '15/03/2024'::date ou falha ou a interpreta de um jeito diferente do que voce espera. A funcao TO_DATE analisa a string contra um modelo explicito que voce mesmo escreve, o que torna o resultado previsivel. Ela aparece com mais frequencia ao importar CSV, em migracoes e ao analisar exportacoes manuais, onde a ordem de dia, mes e ano e conhecida de antemao e precisa ser fixada na propria consulta.

Sintaxe basica

TO_DATE recebe dois argumentos: a string de entrada e um modelo de formato que descreve como essa string esta organizada.

-- Input string, then the format template that describes it
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d;
-- result: 2024-03-15

O modelo e montado a partir de codigos de campo: YYYY e um ano de quatro digitos, MM o mes, DD o dia, Mon o nome curto do mes e Month o completo. Literais como hifens e barras sao escritos como estao:

SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY')   AS eu_style,
       TO_DATE('March 15, 2024', 'Month DD, YYYY') AS verbose,
       TO_DATE('20240315', 'YYYYMMDD')        AS compact;

Por que nao usar so ::date

O cast ::date depende da configuracao datestyle do servidor e entende principalmente strings parecidas com ISO. Para formatos ambiguos ele e perigoso:

-- Ambiguous: is this March 4 or April 3?
SELECT '03/04/2024'::date;        -- depends on server datestyle
SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS as_us;  -- explicit: March 4
SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS as_eu;  -- explicit: April 3

Com TO_DATE a intencao fica codificada na propria consulta e nao depende da configuracao do servidor. Um caso pratico e carregar datas de nascimento de usuarios que chegaram em formato europeu:

-- Imagine a staging table with raw text dates
SELECT id, email, TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_day
FROM users_staging;

Vantagens de um modelo explicito:

• o resultado nao depende do datestyle nem da localidade do servidor;
• a consulta se autodocumenta: da para ver qual formato e esperado;
• funciona igual para layout US, EU ou qualquer formato personalizado.

A armadilha principal: campos fora do intervalo dependem da versao

O detalhe crucial do TO_DATE e que o seu rigor depende da versao do PostgreSQL, e e exatamente ai que as migracoes quebram. Os comentarios abaixo mostram o comportamento antigo anterior ao PostgreSQL 16: o mes 13 e o dia 32 nao geravam erro, mas «transbordavam» para frente (o mes 13 virava janeiro do ano seguinte e o dia 32 o primeiro dia do mes seguinte). Essa promocao silenciosa de uma data invalida em uma valida era a causa classica de bugs dificeis de encontrar.

-- Before PostgreSQL 16: 13 is not a month, but TO_DATE rolled it over
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD');  -- pre-16: 2025-01-01; 16+: errors

-- Same story for day 32
SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD');  -- pre-16: 2024-02-01; 16+: errors

-- Separators in data and template can still mismatch silently
SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD');  -- still parses on any version

A partir do PostgreSQL 16 o comportamento mudou: as duas primeiras consultas nao transbordam mais o excesso, elas geram um erro: date/time field value out of range para o mes 13 e date out of range para o dia 32. Os resultados 2025-01-01 e 2024-02-01 dos comentarios so aparecem no PostgreSQL 15 e anteriores. A terceira consulta, porem, e permissiva em qualquer versao: a divergencia de separadores entre dados e modelo (/ contra -) continua passando sem reclamar. Por isso, se voce precisa de rigor garantido em qualquer versao, nao confie na validacao de intervalos embutida: confira o resultado formatando-o de volta com TO_CHAR:

-- Reject rows where round-trip does not match the input
SELECT raw_signup
FROM users_staging
WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;

Anos de dois digitos

O codigo YY dispara uma regra de janela: o PostgreSQL completa um ano de dois digitos em relacao ao ano atual, o que quase nunca e o que voce quer para datas historicas.

-- YY uses a sliding window around the current year
SELECT TO_DATE('15-03-49', 'DD-MM-YY');  -- 2049, maybe fine
SELECT TO_DATE('15-03-99', 'DD-MM-YY');  -- 1999, sliding-window guess

-- Force the century explicitly with RR or just demand 4 digits
SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY');  -- no guessing

Dica: exija YYYY sempre que puder e normalize os dados de origem para anos de quatro digitos para eliminar de vez a adivinhacao.

TO_DATE versus TO_TIMESTAMP

TO_DATE descarta a parte da hora: o resultado e sempre do tipo date. Se a string tem horas, minutos e segundos, use TO_TIMESTAMP, caso contrario voce perde precisao em silencio.

-- TO_DATE drops the time portion entirely
SELECT TO_DATE('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');  -- 2024-03-15

-- TO_TIMESTAMP keeps hours, minutes, seconds
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00

Diferencas entre bancos:

• O MySQL nao tem TO_DATE; use STR_TO_DATE('15/03/2024', '%d/%m/%Y') com codigos de porcentagem, e em modo estrito ele retorna NULL numa data invalida.
• O ClickHouse oferece parseDateTimeBestEffort e uma parseDateTime estrita com modelo no estilo Java; use o sufixo OrNull por seguranca.

-- MySQL equivalent uses percent-style format codes
SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;

Ao mover a analise de datas entre PostgreSQL, MySQL e ClickHouse, o perigo real nao e a sintaxe do TO_DATE, mas a diferenca em como cada motor trata os valores invalidos. O PostgreSQL anterior ao 16 transborda em silencio o mes 13 e o dia 32, o PostgreSQL 16+ gera um erro com eles, o MySQL em modo estrito (STR_TO_DATE) retorna NULL e o ClickHouse depende de voce ter escolhido parseDateTime ou a variante com sufixo OrNull. Por isso, antes de migrar, passe por todos os motores a mesma pequena tabela de casos limite: NULL, string vazia, mes 13, dia 32, ano de dois digitos e separadores que nao coincidem. No caminho feliz de datas ISO limpas todos os motores concordam; eles divergem justamente nessas linhas.

Repare separadamente que TO_DATE(coluna, ...) numa clausula WHERE e uma funcao sobre a coluna e bloqueia um indice comum sobre ela. Se voce filtra pela data analisada em tabelas grandes, analise as strings uma so vez na carga para uma coluna do tipo date e construa o indice sobre ela, em vez de chamar TO_DATE em cada consulta.

Resumo: use TO_DATE com um modelo explicito para formatos ambiguos, nao confie no ::date com strings que nao sao ISO, lembre-se de que campos fora do intervalo transbordam em silencio antes do PostgreSQL 16 mas geram um erro no 16+, tenha em mente a armadilha do ano de dois digitos e use TO_TIMESTAMP quando o valor carregar uma hora.