Conversión de formatos de fecha

El mundo de las bases de datos y el frontend muchas veces no se ponen de acuerdo sobre cómo deberían verse las fechas. PostgreSQL puede guardar fechas como DATE, TIMESTAMP o incluso TIMESTAMPTZ, pero ese formato no siempre es el ideal para mostrarle a un usuario. Por ejemplo, en vez del estándar 2023-10-01 12:30:45, los diseñadores pueden querer ver 01 de octubre de 2023, 12:30. Y a veces hay que formatear la fecha para informes o para un API.

Para convertir fechas a string y viceversa en PostgreSQL existen las funciones TO_CHAR() y TO_DATE().

Función TO_CHAR()

TO_CHAR() es tu mejor colega cuando necesitas transformar datos temporales en un formato string legible para humanos. Recibe una fecha o timestamp y la formatea según el formato que le digas.

Sintaxis

TO_CHAR(value, format)

• value — la fecha o timestamp que quieres convertir.
• format — el string con el patrón de formato, o sea, cómo quieres mostrar la fecha.

Ejemplos de formatos

Puedes ver la lista completa de formatos en la documentación oficial de PostgreSQL.

Ejemplos de uso de TO_CHAR()

Formatear fecha para un informe

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Resultado: '09.10.2023'

Mostrar la hora en formato de 12 horas

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Resultado: '03:45 PM'

Mostrar el mes en palabras

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Resultado: 'October '

Ojo: PostgreSQL mete un espacio al final. ¡Es una feature, no un bug! Si quieres quitar los espacios, usa la función TRIM():

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Crear un formato personalizado

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Resultado: '2023/10/09 15:45:30'

Formatear para la interfaz de usuario

SELECT TO_CHAR(NOW(), 'DD "de octubre" YYYY года') AS user_friendly_date;
-- Resultado: '09 de octubre 2023 года'

Función TO_DATE()

TO_DATE() hace lo contrario: toma un string y lo convierte al tipo de dato DATE. ¿Para qué sirve esto? Por ejemplo, un usuario puede meter una fecha en formato 01-10-2023, y PostgreSQL tiene que "entender" qué fecha es esa.

Sintaxis

TO_DATE(value, format)

• value — el string que contiene la fecha.
• format — el string con el patrón que describe el formato del string.

Ejemplos de uso de TO_DATE()

Convertir un string a fecha

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Resultado: '2023-10-01' (tipo de dato: DATE)

Comparar una fecha en string con una fecha en la tabla

Supón que tienes una tabla appointments con una columna appointment_date de tipo DATE. El usuario mete la fecha como string:

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Formato incorrecto

Importante: si el formato del string no coincide con el patrón, ¡te salta un error! Por ejemplo:

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Error: formato de entrada incorrecto

Validar datos del usuario

Supón que creas una tabla para guardar pedidos, donde la fecha la mete el usuario:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Insertar datos convirtiendo el string a fecha
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Ejemplos prácticos

Formatear un informe. En la tabla sales se guarda la fecha de venta en la columna sale_date (tipo TIMESTAMP). Hay que sacar un informe donde las fechas estén en formato DD.MM.YYYY.

-- Ejemplo de datos
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Informe
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Convertir datos del usuario. Supón que el usuario mete la fecha en formato string MM/DD/YYYY. Hay que convertirla a DATE para guardarla en el sistema.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Errores típicos y recomendaciones

Formato incorrecto. Es muy común que el formato del string no coincida con el patrón. Por ejemplo, si el usuario mete la fecha como 01-10-2023 pero el formato es MM/DD/YYYY, PostgreSQL te va a dar error. Recomendación: valida siempre la entrada del usuario antes de mandarla al SQL.

Espacios en los formatos de TO_CHAR(). Algunos formatos como MONTH añaden espacios. Si eso te molesta, usa la función TRIM().

Errores al parsear strings. Si el string tiene símbolos raros o un formato inesperado, PostgreSQL no va a poder convertirlo. Recomendación: usa expresiones regulares o validaciones extra antes de meter los datos en la base.

Uso incorrecto de formatos de tiempo. Por ejemplo, intentar procesar un timestamp con un patrón de DATE. Recomendación: asegúrate de que los tipos de datos que usas encajan con lo que necesitas.

Las funciones TO_CHAR() y TO_DATE() te abren un montón de posibilidades para trabajar con datos temporales. Puedes crear formatos cómodos para informes, convertir la entrada del usuario y hacer tus consultas SQL mucho más legibles. En la vida real, estas funciones se usan un montón para visualizar datos, crear informes, integrarse con otros sistemas y preparar interfaces de usuario.