Tutorial SQL: Funciones de fecha y hora

Componentes temporales

Trabajar con fechas y horas en SQL requiere entender los distintos componentes que conforman un valor temporal. Estos componentes nos permiten extraer, manipular y analizar partes específicas de fechas y horas en nuestras consultas.

Tanto MySQL como PostgreSQL ofrecen funciones para extraer y manipular estos componentes temporales, aunque con algunas diferencias en su sintaxis y comportamiento.

Tipos de datos temporales

Antes de trabajar con componentes, es importante conocer los principales tipos de datos temporales:

• DATE: Almacena solo fechas (YYYY-MM-DD)
• TIME: Almacena solo horas (HH:MM:SS)
• DATETIME/TIMESTAMP: Almacena fecha y hora

En MySQL:

CREATE TABLE eventos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    solo_fecha DATE,
    solo_hora TIME,
    fecha_hora DATETIME
);

En PostgreSQL:

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    solo_fecha DATE,
    solo_hora TIME,
    fecha_hora TIMESTAMP
);

Extracción de componentes

Podemos extraer componentes específicos como año, mes, día, hora, minuto o segundo de un valor temporal.

En MySQL

MySQL ofrece la función EXTRACT() y funciones específicas para cada componente:

-- Usando EXTRACT()
SELECT 
    fecha_evento,
    EXTRACT(YEAR FROM fecha_evento) AS año,
    EXTRACT(MONTH FROM fecha_evento) AS mes,
    EXTRACT(DAY FROM fecha_evento) AS día
FROM eventos;

-- Usando funciones específicas
SELECT 
    fecha_evento,
    YEAR(fecha_evento) AS año,
    MONTH(fecha_evento) AS mes,
    DAY(fecha_evento) AS día,
    HOUR(fecha_evento) AS hora,
    MINUTE(fecha_evento) AS minuto,
    SECOND(fecha_evento) AS segundo
FROM eventos;

MySQL también ofrece funciones para extraer componentes más específicos:

SELECT 
    fecha_evento,
    DAYOFWEEK(fecha_evento) AS dia_semana, -- 1=Domingo, 2=Lunes, etc.
    DAYOFMONTH(fecha_evento) AS dia_mes,
    DAYOFYEAR(fecha_evento) AS dia_año,
    QUARTER(fecha_evento) AS trimestre,
    WEEK(fecha_evento) AS semana
FROM eventos;

En PostgreSQL

PostgreSQL utiliza principalmente la función EXTRACT() y la función date_part():

-- Usando EXTRACT()
SELECT 
    fecha_evento,
    EXTRACT(YEAR FROM fecha_evento) AS año,
    EXTRACT(MONTH FROM fecha_evento) AS mes,
    EXTRACT(DAY FROM fecha_evento) AS día,
    EXTRACT(HOUR FROM fecha_evento) AS hora,
    EXTRACT(MINUTE FROM fecha_evento) AS minuto,
    EXTRACT(SECOND FROM fecha_evento) AS segundo
FROM eventos;

-- Usando date_part()
SELECT 
    fecha_evento,
    date_part('year', fecha_evento) AS año,
    date_part('month', fecha_evento) AS mes,
    date_part('day', fecha_evento) AS día
FROM eventos;

PostgreSQL ofrece componentes adicionales:

SELECT 
    fecha_evento,
    EXTRACT(DOW FROM fecha_evento) AS dia_semana, -- 0=Domingo, 1=Lunes, etc.
    EXTRACT(QUARTER FROM fecha_evento) AS trimestre,
    EXTRACT(WEEK FROM fecha_evento) AS semana,
    EXTRACT(CENTURY FROM fecha_evento) AS siglo,
    EXTRACT(DECADE FROM fecha_evento) AS década,
    EXTRACT(MILLENNIUM FROM fecha_evento) AS milenio
FROM eventos;

Componentes de fecha

Los componentes de fecha más comunes incluyen:

• YEAR: Año (4 dígitos)
• MONTH: Mes (1-12)
• DAY: Día del mes (1-31)
• QUARTER: Trimestre (1-4)
• WEEK: Semana del año

Ejemplo práctico para agrupar ventas por trimestre:

-- MySQL
SELECT 
    QUARTER(fecha_venta) AS trimestre,
    SUM(monto) AS ventas_totales
FROM ventas
WHERE YEAR(fecha_venta) = 2023
GROUP BY QUARTER(fecha_venta)
ORDER BY trimestre;

-- PostgreSQL
SELECT 
    EXTRACT(QUARTER FROM fecha_venta) AS trimestre,
    SUM(monto) AS ventas_totales
FROM ventas
WHERE EXTRACT(YEAR FROM fecha_venta) = 2023
GROUP BY EXTRACT(QUARTER FROM fecha_venta)
ORDER BY trimestre;

Componentes de hora

Los componentes de hora incluyen:

• HOUR: Hora (0-23)
• MINUTE: Minuto (0-59)
• SECOND: Segundo (0-59)
• MICROSECOND: Microsegundo (MySQL)
• MILLISECOND: Milisegundo (PostgreSQL)

Ejemplo para analizar el tráfico de un sitio web por hora:

-- MySQL
SELECT 
    HOUR(hora_acceso) AS hora_del_dia,
    COUNT(*) AS numero_visitas
FROM accesos_web
WHERE DATE(hora_acceso) = '2023-09-15'
GROUP BY HOUR(hora_acceso)
ORDER BY hora_del_dia;

-- PostgreSQL
SELECT 
    EXTRACT(HOUR FROM hora_acceso) AS hora_del_dia,
    COUNT(*) AS numero_visitas
FROM accesos_web
WHERE hora_acceso::DATE = '2023-09-15'
GROUP BY EXTRACT(HOUR FROM hora_acceso)
ORDER BY hora_del_dia;

Componentes de semana

Tanto MySQL como PostgreSQL ofrecen funciones para trabajar con semanas:

-- MySQL
SELECT 
    YEARWEEK(fecha_evento) AS año_semana, -- Formato YYYYWW
    WEEKDAY(fecha_evento) AS dia_semana, -- 0=Lunes, 1=Martes, etc.
    DAYNAME(fecha_evento) AS nombre_dia
FROM eventos;

-- PostgreSQL
SELECT 
    TO_CHAR(fecha_evento, 'IYYY-IW') AS año_semana,
    EXTRACT(DOW FROM fecha_evento) AS dia_semana, -- 0=Domingo, 1=Lunes, etc.
    TO_CHAR(fecha_evento, 'Day') AS nombre_dia
FROM eventos;

Componentes de intervalo

Los intervalos son útiles para representar duraciones o periodos de tiempo:

En MySQL:

-- Crear un intervalo
SELECT INTERVAL 2 YEAR;
SELECT INTERVAL 3 MONTH;
SELECT INTERVAL 5 DAY;
SELECT INTERVAL '2:30' HOUR_MINUTE;

-- Extraer componentes de un intervalo
SELECT EXTRACT(DAY FROM INTERVAL '2 3:4:5.678' DAY_SECOND);

En PostgreSQL:

-- Crear un intervalo
SELECT INTERVAL '2 years';
SELECT INTERVAL '3 months';
SELECT INTERVAL '5 days';
SELECT INTERVAL '2 hours 30 minutes';

-- Extraer componentes de un intervalo
SELECT EXTRACT(DAY FROM INTERVAL '2 days 3 hours');

Uso práctico de componentes temporales

Filtrado por componentes específicos

-- Encontrar todos los pedidos realizados en fin de semana
-- MySQL
SELECT * FROM pedidos 
WHERE WEEKDAY(fecha_pedido) >= 5; -- 5=Sábado, 6=Domingo

-- PostgreSQL
SELECT * FROM pedidos 
WHERE EXTRACT(DOW FROM fecha_pedido) IN (0, 6); -- 0=Domingo, 6=Sábado

Agrupación por periodos de tiempo

-- Ventas por mes en un año específico
-- MySQL
SELECT 
    MONTH(fecha_venta) AS mes,
    MONTHNAME(fecha_venta) AS nombre_mes,
    SUM(monto) AS total_ventas
FROM ventas
WHERE YEAR(fecha_venta) = 2023
GROUP BY MONTH(fecha_venta), MONTHNAME(fecha_venta)
ORDER BY mes;

-- PostgreSQL
SELECT 
    EXTRACT(MONTH FROM fecha_venta) AS mes,
    TO_CHAR(fecha_venta, 'Month') AS nombre_mes,
    SUM(monto) AS total_ventas
FROM ventas
WHERE EXTRACT(YEAR FROM fecha_venta) = 2023
GROUP BY EXTRACT(MONTH FROM fecha_venta), TO_CHAR(fecha_venta, 'Month')
ORDER BY mes;

Análisis de patrones temporales

-- Análisis de actividad por hora del día
-- MySQL
SELECT 
    HOUR(timestamp) AS hora,
    COUNT(*) AS actividad
FROM actividad_usuarios
GROUP BY HOUR(timestamp)
ORDER BY hora;

-- PostgreSQL
SELECT 
    EXTRACT(HOUR FROM timestamp) AS hora,
    COUNT(*) AS actividad
FROM actividad_usuarios
GROUP BY EXTRACT(HOUR FROM timestamp)
ORDER BY hora;

Diferencias entre MySQL y PostgreSQL

Es importante tener en cuenta algunas diferencias clave:

• Indexación de días de semana: MySQL considera 0=Lunes en WEEKDAY() pero 1=Domingo en DAYOFWEEK(), mientras que PostgreSQL usa 0=Domingo en EXTRACT(DOW).

• Nombres de funciones: MySQL tiene funciones específicas como YEAR(), MONTH(), mientras que PostgreSQL utiliza principalmente EXTRACT() y date_part().

• Formato de salida: PostgreSQL ofrece más flexibilidad con la función TO_CHAR() para formatear componentes temporales.

Cálculos de tiempo

Los cálculos de tiempo son operaciones fundamentales cuando trabajamos con datos temporales en SQL. Nos permiten determinar duraciones entre fechas, sumar o restar intervalos de tiempo, y realizar análisis basados en periodos temporales. Tanto MySQL como PostgreSQL ofrecen funciones específicas para estos cálculos, aunque con diferencias en su sintaxis.

Diferencia entre fechas

Calcular el tiempo transcurrido entre dos fechas es una operación común en bases de datos.

En MySQL

MySQL ofrece varias funciones para calcular diferencias temporales:

-- Diferencia en días
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS dias_transcurridos;

-- Diferencia en segundos
SELECT TIMESTAMPDIFF(SECOND, '2023-01-01 10:00:00', '2023-01-01 12:30:45') AS segundos_transcurridos;

-- Diferencia en otras unidades (MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR)
SELECT 
    TIMESTAMPDIFF(HOUR, '2023-01-01 08:30:00', '2023-01-02 14:45:00') AS horas_transcurridas,
    TIMESTAMPDIFF(MONTH, '2022-03-15', '2023-08-20') AS meses_transcurridos;

En PostgreSQL

PostgreSQL utiliza el operador de resta (-) entre fechas y la función AGE():

-- Diferencia en días (devuelve un valor numérico)
SELECT '2023-12-31'::DATE - '2023-01-01'::DATE AS dias_transcurridos;

-- Diferencia como intervalo (devuelve un intervalo completo)
SELECT AGE('2023-12-31', '2023-01-01') AS tiempo_transcurrido;

-- Extraer componentes específicos del intervalo
SELECT 
    EXTRACT(DAY FROM AGE('2023-12-31', '2023-01-01')) AS dias,
    EXTRACT(MONTH FROM AGE('2023-12-31', '2023-01-01')) AS meses,
    EXTRACT(YEAR FROM AGE('2023-12-31', '2023-01-01')) AS años;

Suma y resta de intervalos

Añadir o restar periodos de tiempo a una fecha es otra operación habitual.

En MySQL

MySQL utiliza las funciones DATE_ADD() y DATE_SUB(), o el operador + con INTERVAL:

-- Añadir intervalos
SELECT 
    fecha_actual,
    DATE_ADD(fecha_actual, INTERVAL 7 DAY) AS fecha_mas_7_dias,
    fecha_actual + INTERVAL 3 MONTH AS fecha_mas_3_meses,
    fecha_actual + INTERVAL '1-6' YEAR_MONTH AS fecha_mas_1_año_6_meses
FROM (SELECT CURRENT_DATE() AS fecha_actual) AS t;

-- Restar intervalos
SELECT 
    fecha_actual,
    DATE_SUB(fecha_actual, INTERVAL 14 DAY) AS fecha_menos_14_dias,
    fecha_actual - INTERVAL 1 YEAR AS fecha_menos_1_año
FROM (SELECT CURRENT_DATE() AS fecha_actual) AS t;

MySQL permite combinar intervalos en una sola operación:

SELECT 
    CURRENT_TIMESTAMP() AS ahora,
    CURRENT_TIMESTAMP() + INTERVAL 2 DAY + INTERVAL 4 HOUR AS futuro;

En PostgreSQL

PostgreSQL utiliza el operador + y - con intervalos:

-- Añadir intervalos
SELECT 
    CURRENT_DATE AS fecha_actual,
    CURRENT_DATE + INTERVAL '7 days' AS fecha_mas_7_dias,
    CURRENT_DATE + INTERVAL '3 months' AS fecha_mas_3_meses,
    CURRENT_DATE + INTERVAL '1 year 6 months' AS fecha_mas_1_año_6_meses;

-- Restar intervalos
SELECT 
    CURRENT_DATE AS fecha_actual,
    CURRENT_DATE - INTERVAL '14 days' AS fecha_menos_14_dias,
    CURRENT_DATE - INTERVAL '1 year' AS fecha_menos_1_año;

Cálculo de fechas relativas

A menudo necesitamos calcular fechas relativas como "primer día del mes" o "último día del año".

En MySQL

-- Primer día del mes actual
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFMONTH(CURRENT_DATE())-1 DAY) AS primer_dia_mes;

-- Último día del mes actual
SELECT LAST_DAY(CURRENT_DATE()) AS ultimo_dia_mes;

-- Primer día del año actual
SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') AS primer_dia_año;

-- Último día del año actual
SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-12-31') AS ultimo_dia_año;

En PostgreSQL

-- Primer día del mes actual
SELECT DATE_TRUNC('month', CURRENT_DATE) AS primer_dia_mes;

-- Último día del mes actual
SELECT 
    (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day')::DATE 
    AS ultimo_dia_mes;

-- Primer día del año actual
SELECT DATE_TRUNC('year', CURRENT_DATE) AS primer_dia_año;

-- Último día del año actual
SELECT 
    (DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' - INTERVAL '1 day')::DATE 
    AS ultimo_dia_año;

La función DATE_TRUNC() de PostgreSQL es muy útil para obtener el inicio de un periodo:

SELECT 
    CURRENT_TIMESTAMP AS ahora,
    DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS inicio_hora_actual,
    DATE_TRUNC('day', CURRENT_TIMESTAMP) AS inicio_dia_actual,
    DATE_TRUNC('week', CURRENT_TIMESTAMP) AS inicio_semana_actual,
    DATE_TRUNC('month', CURRENT_TIMESTAMP) AS inicio_mes_actual,
    DATE_TRUNC('quarter', CURRENT_TIMESTAMP) AS inicio_trimestre_actual,
    DATE_TRUNC('year', CURRENT_TIMESTAMP) AS inicio_año_actual;

Cálculos con periodos de negocio

Los cálculos de tiempo son especialmente útiles para análisis de negocio.

Cálculo de antigüedad

-- MySQL: Calcular antigüedad de empleados en años
SELECT 
    nombre,
    fecha_contratacion,
    TIMESTAMPDIFF(YEAR, fecha_contratacion, CURRENT_DATE()) AS años_antiguedad
FROM empleados
ORDER BY años_antiguedad DESC;

-- PostgreSQL: Calcular antigüedad de empleados
SELECT 
    nombre,
    fecha_contratacion,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, fecha_contratacion)) AS años_antiguedad
FROM empleados
ORDER BY años_antiguedad DESC;

Análisis de periodos de tiempo

-- MySQL: Ventas por periodo de 30 días
SELECT 
    DATE(fecha_venta) AS fecha,
    SUM(monto) AS ventas_diarias,
    SUM(SUM(monto)) OVER (
        ORDER BY DATE(fecha_venta) 
        RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW
    ) AS ventas_ultimos_30_dias
FROM ventas
GROUP BY DATE(fecha_venta)
ORDER BY fecha;

-- PostgreSQL: Ventas por periodo de 30 días
SELECT 
    fecha_venta::DATE AS fecha,
    SUM(monto) AS ventas_diarias,
    SUM(SUM(monto)) OVER (
        ORDER BY fecha_venta::DATE 
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS ventas_ultimos_30_dias
FROM ventas
GROUP BY fecha_venta::DATE
ORDER BY fecha;

Cálculo de días hábiles

Un cálculo común es determinar días hábiles (excluyendo fines de semana y festivos).

En MySQL

-- Días hábiles entre dos fechas (excluyendo fines de semana)
CREATE FUNCTION dias_habiles(fecha_inicio DATE, fecha_fin DATE) 
RETURNS INT
BEGIN
    DECLARE dias INT DEFAULT 0;
    DECLARE fecha_actual DATE DEFAULT fecha_inicio;
    
    WHILE fecha_actual <= fecha_fin DO
        IF WEEKDAY(fecha_actual) < 5 THEN -- 0-4 son lunes a viernes
            SET dias = dias + 1;
        END IF;
        SET fecha_actual = DATE_ADD(fecha_actual, INTERVAL 1 DAY);
    END WHILE;
    
    RETURN dias;
END;

-- Uso de la función
SELECT dias_habiles('2023-01-01', '2023-01-31') AS dias_laborables_enero;

En PostgreSQL

-- Días hábiles entre dos fechas (excluyendo fines de semana)
CREATE OR REPLACE FUNCTION dias_habiles(fecha_inicio DATE, fecha_fin DATE) 
RETURNS INTEGER AS $$
DECLARE
    dias INTEGER := 0;
    fecha_actual DATE := fecha_inicio;
BEGIN
    WHILE fecha_actual <= fecha_fin LOOP
        IF EXTRACT(DOW FROM fecha_actual) NOT IN (0, 6) THEN -- 0=domingo, 6=sábado
            dias := dias + 1;
        END IF;
        fecha_actual := fecha_actual + INTERVAL '1 day';
    END LOOP;
    
    RETURN dias;
END;
$$ LANGUAGE plpgsql;

-- Uso de la función
SELECT dias_habiles('2023-01-01', '2023-01-31') AS dias_laborables_enero;

Operaciones con zonas horarias

El manejo de zonas horarias es crucial para aplicaciones globales.

En MySQL

-- Convertir entre zonas horarias
SELECT 
    NOW() AS hora_servidor,
    CONVERT_TZ(NOW(), 'UTC', 'America/New_York') AS hora_nueva_york,
    CONVERT_TZ(NOW(), 'UTC', 'Europe/Madrid') AS hora_madrid,
    CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') AS hora_tokio;

En PostgreSQL

-- Convertir entre zonas horarias
SELECT 
    NOW() AS hora_servidor,
    NOW() AT TIME ZONE 'UTC' AS hora_utc,
    NOW() AT TIME ZONE 'America/New_York' AS hora_nueva_york,
    NOW() AT TIME ZONE 'Europe/Madrid' AS hora_madrid,
    NOW() AT TIME ZONE 'Asia/Tokyo' AS hora_tokio;

Casos prácticos de cálculos de tiempo

Cálculo de duración de sesiones

-- MySQL: Duración promedio de sesiones de usuario
SELECT 
    usuario_id,
    AVG(TIMESTAMPDIFF(MINUTE, inicio_sesion, fin_sesion)) AS duracion_promedio_minutos
FROM sesiones
GROUP BY usuario_id
ORDER BY duracion_promedio_minutos DESC;

-- PostgreSQL: Duración promedio de sesiones de usuario
SELECT 
    usuario_id,
    AVG(EXTRACT(EPOCH FROM (fin_sesion - inicio_sesion))/60) AS duracion_promedio_minutos
FROM sesiones
GROUP BY usuario_id
ORDER BY duracion_promedio_minutos DESC;

Análisis de tiempo de respuesta

-- MySQL: Tiempo promedio de respuesta a tickets por departamento
SELECT 
    departamento,
    AVG(TIMESTAMPDIFF(HOUR, fecha_creacion, fecha_respuesta)) AS tiempo_respuesta_horas
FROM tickets
WHERE fecha_respuesta IS NOT NULL
GROUP BY departamento
ORDER BY tiempo_respuesta_horas;

-- PostgreSQL: Tiempo promedio de respuesta a tickets por departamento
SELECT 
    departamento,
    AVG(EXTRACT(EPOCH FROM (fecha_respuesta - fecha_creacion))/3600) AS tiempo_respuesta_horas
FROM tickets
WHERE fecha_respuesta IS NOT NULL
GROUP BY departamento
ORDER BY tiempo_respuesta_horas;

Cálculo de fechas de vencimiento

-- MySQL: Calcular fechas de vencimiento de facturas (30 días)
SELECT 
    id_factura,
    fecha_emision,
    DATE_ADD(fecha_emision, INTERVAL 30 DAY) AS fecha_vencimiento,
    CASE 
        WHEN CURRENT_DATE() > DATE_ADD(fecha_emision, INTERVAL 30 DAY) THEN 'Vencida'
        ELSE 'Pendiente'
    END AS estado
FROM facturas
WHERE pagada = FALSE;

-- PostgreSQL: Calcular fechas de vencimiento de facturas (30 días)
SELECT 
    id_factura,
    fecha_emision,
    fecha_emision + INTERVAL '30 days' AS fecha_vencimiento,
    CASE 
        WHEN CURRENT_DATE > fecha_emision + INTERVAL '30 days' THEN 'Vencida'
        ELSE 'Pendiente'
    END AS estado
FROM facturas
WHERE pagada = FALSE;

Los cálculos de tiempo son herramientas esenciales para el análisis de datos temporales y la automatización de procesos basados en fechas. Dominar estas funciones te permitirá implementar lógicas de negocio complejas relacionadas con periodos, duraciones y programaciones en tus aplicaciones de bases de datos.

Formatos de fecha/hora

El formato de fechas y horas es un aspecto fundamental cuando trabajamos con datos temporales en SQL. La forma en que presentamos y convertimos estos valores determina cómo los usuarios finales interpretan la información y cómo interactúan nuestras aplicaciones con los datos temporales. Tanto MySQL como PostgreSQL ofrecen funciones específicas para formatear y convertir fechas y horas según nuestras necesidades.

Funciones de formateo en MySQL

MySQL proporciona la función DATE_FORMAT() como herramienta principal para dar formato a fechas y horas. Esta función permite convertir valores temporales en cadenas de texto con el formato deseado mediante especificadores.

SELECT DATE_FORMAT('2023-09-15 14:30:25', '%d/%m/%Y %H:%i:%s') AS fecha_formateada;
-- Resultado: '15/09/2023 14:30:25'

Los especificadores de formato más comunes en MySQL incluyen:

• %d: Día del mes con ceros iniciales (01-31)
• %j: Día del año (001-366)
• %w: Día de la semana (0=Domingo, 6=Sábado)
• %W: Nombre del día de la semana (Sunday-Saturday)
• %m: Mes con ceros iniciales (01-12)
• %M: Nombre del mes (January-December)
• %y: Año en formato de 2 dígitos
• %Y: Año en formato de 4 dígitos
• %H: Hora en formato 24h (00-23)
• %h: Hora en formato 12h (01-12)
• %i: Minutos (00-59)
• %s: Segundos (00-59)
• %p: AM o PM

Ejemplos prácticos de formateo en MySQL:

-- Formato de fecha europea (día/mes/año)
SELECT DATE_FORMAT(fecha_nacimiento, '%d/%m/%Y') AS fecha_europea
FROM empleados;

-- Formato de fecha americana (mes/día/año)
SELECT DATE_FORMAT(fecha_nacimiento, '%m/%d/%Y') AS fecha_americana
FROM empleados;

-- Fecha con nombre de mes y día de la semana
SELECT DATE_FORMAT(fecha_evento, '%W, %d de %M de %Y') AS fecha_descriptiva
FROM eventos;
-- Resultado ejemplo: 'Friday, 15 de September de 2023'

-- Hora en formato 12 horas con AM/PM
SELECT DATE_FORMAT(hora_registro, '%h:%i %p') AS hora_12h
FROM registros;
-- Resultado ejemplo: '02:30 PM'

MySQL también ofrece la función TIME_FORMAT() específica para valores de tipo TIME:

SELECT TIME_FORMAT('14:30:25', '%h:%i %p') AS hora_formateada;
-- Resultado: '02:30 PM'

Funciones de formateo en PostgreSQL

PostgreSQL utiliza principalmente la función TO_CHAR() para formatear fechas y horas. Esta función es más versátil ya que también puede usarse para formatear números y otros tipos de datos.

SELECT TO_CHAR(TIMESTAMP '2023-09-15 14:30:25', 'DD/MM/YYYY HH24:MI:SS') AS fecha_formateada;
-- Resultado: '15/09/2023 14:30:25'

Los patrones de formato más comunes en PostgreSQL incluyen:

• DD: Día del mes con ceros iniciales (01-31)
• DDD: Día del año (001-366)
• D: Día de la semana (1-7, donde 1 es lunes)
• DAY: Nombre completo del día de la semana
• MM: Mes con ceros iniciales (01-12)
• MONTH: Nombre completo del mes
• MON: Abreviatura del nombre del mes
• YY: Año en formato de 2 dígitos
• YYYY: Año en formato de 4 dígitos
• HH24: Hora en formato 24h (00-23)
• HH12: Hora en formato 12h (01-12)
• MI: Minutos (00-59)
• SS: Segundos (00-59)
• AM o PM: Indicador de mañana/tarde

Ejemplos prácticos de formateo en PostgreSQL:

-- Formato de fecha europea (día/mes/año)
SELECT TO_CHAR(fecha_nacimiento, 'DD/MM/YYYY') AS fecha_europea
FROM empleados;

-- Formato de fecha americana (mes/día/año)
SELECT TO_CHAR(fecha_nacimiento, 'MM/DD/YYYY') AS fecha_americana
FROM empleados;

-- Fecha con nombre de mes y día de la semana
SELECT TO_CHAR(fecha_evento, 'Day, DD de Month de YYYY') AS fecha_descriptiva
FROM eventos;
-- Resultado ejemplo: 'Friday, 15 de September de 2023'

-- Hora en formato 12 horas con AM/PM
SELECT TO_CHAR(hora_registro, 'HH12:MI AM') AS hora_12h
FROM registros;
-- Resultado ejemplo: '02:30 PM'

PostgreSQL permite personalizar aún más el formato con opciones adicionales:

-- Eliminar espacios en blanco de relleno
SELECT TO_CHAR(TIMESTAMP '2023-09-15', 'FMDay, DD de Month de YYYY') AS fecha_sin_espacios;

-- Usar nombres en español (requiere configuración de locale)
SELECT TO_CHAR(TIMESTAMP '2023-09-15', 'TMDay, DD de TMMonth de YYYY') AS fecha_en_español;

Conversión entre cadenas y fechas

Además de formatear fechas para mostrarlas, a menudo necesitamos convertir cadenas de texto en valores de fecha y hora.

En MySQL

MySQL utiliza las funciones STR_TO_DATE() y CAST():

-- Convertir cadena a fecha usando STR_TO_DATE()
SELECT STR_TO_DATE('15/09/2023', '%d/%m/%Y') AS fecha_convertida;

-- Convertir cadena a fecha usando CAST()
SELECT CAST('2023-09-15' AS DATE) AS fecha_convertida;

-- Convertir cadena a datetime
SELECT STR_TO_DATE('15/09/2023 14:30:25', '%d/%m/%Y %H:%i:%s') AS datetime_convertido;

MySQL también ofrece funciones implícitas de conversión:

-- Conversión implícita (formato ISO)
SELECT '2023-09-15' + INTERVAL 1 DAY AS dia_siguiente;

En PostgreSQL

PostgreSQL utiliza las funciones TO_DATE(), TO_TIMESTAMP() y operadores de conversión de tipo:

-- Convertir cadena a fecha usando TO_DATE()
SELECT TO_DATE('15/09/2023', 'DD/MM/YYYY') AS fecha_convertida;

-- Convertir cadena a timestamp usando TO_TIMESTAMP()
SELECT TO_TIMESTAMP('15/09/2023 14:30:25', 'DD/MM/YYYY HH24:MI:SS') AS timestamp_convertido;

-- Convertir usando operadores de conversión de tipo
SELECT '2023-09-15'::DATE AS fecha_convertida;
SELECT '2023-09-15 14:30:25'::TIMESTAMP AS timestamp_convertido;

Formatos ISO y estándares

Tanto MySQL como PostgreSQL soportan formatos ISO 8601 para fechas y horas, que son ideales para intercambio de datos entre sistemas.

-- MySQL: Formato ISO
SELECT DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%s') AS fecha_iso;

-- PostgreSQL: Formato ISO
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') AS fecha_iso;

El formato ISO 8601 estándar para fechas y horas es: YYYY-MM-DDThh:mm:ss.

Formateo para diferentes locales y zonas horarias

Localización en MySQL

MySQL permite mostrar nombres de meses y días en diferentes idiomas mediante la configuración de la sesión:

-- Configurar el locale para español
SET lc_time_names = 'es_ES';

-- Mostrar fecha con nombres en español
SELECT DATE_FORMAT(NOW(), '%W, %d de %M de %Y') AS fecha_en_español;
-- Resultado ejemplo: 'Viernes, 15 de Septiembre de 2023'

Localización en PostgreSQL

PostgreSQL maneja la localización a través de la configuración LC_TIME:

-- Configurar el locale para español
SET lc_time TO 'es_ES.UTF-8';

-- Mostrar fecha con nombres en español
SELECT TO_CHAR(CURRENT_DATE, 'Day, DD de Month de YYYY') AS fecha_en_español;
-- Resultado ejemplo: 'Viernes, 15 de Septiembre de 2023'

Casos prácticos de formateo

Generación de informes con fechas formateadas

-- MySQL: Informe de ventas mensuales con formato de fecha legible
SELECT 
    DATE_FORMAT(fecha_venta, '%M %Y') AS mes_año,
    SUM(monto) AS total_ventas
FROM ventas
GROUP BY DATE_FORMAT(fecha_venta, '%Y-%m')
ORDER BY MIN(fecha_venta);

-- PostgreSQL: Informe de ventas mensuales con formato de fecha legible
SELECT 
    TO_CHAR(fecha_venta, 'Month YYYY') AS mes_año,
    SUM(monto) AS total_ventas
FROM ventas
GROUP BY TO_CHAR(fecha_venta, 'YYYY-MM'), TO_CHAR(fecha_venta, 'Month YYYY')
ORDER BY MIN(fecha_venta);

Formateo condicional de fechas

-- MySQL: Formateo condicional según antigüedad
SELECT 
    nombre_producto,
    fecha_creacion,
    CASE 
        WHEN fecha_creacion > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) 
        THEN CONCAT('Nuevo: ', DATE_FORMAT(fecha_creacion, '%d/%m/%Y'))
        ELSE DATE_FORMAT(fecha_creacion, '%d/%m/%Y')
    END AS estado_producto
FROM productos;

-- PostgreSQL: Formateo condicional según antigüedad
SELECT 
    nombre_producto,
    fecha_creacion,
    CASE 
        WHEN fecha_creacion > CURRENT_DATE - INTERVAL '7 days' 
        THEN 'Nuevo: ' || TO_CHAR(fecha_creacion, 'DD/MM/YYYY')
        ELSE TO_CHAR(fecha_creacion, 'DD/MM/YYYY')
    END AS estado_producto
FROM productos;

Formateo para exportación de datos

Cuando exportamos datos a otros sistemas, el formato de fecha es crucial:

-- MySQL: Exportación a CSV con formato ISO
SELECT 
    id_pedido,
    cliente_id,
    DATE_FORMAT(fecha_pedido, '%Y-%m-%d') AS fecha,
    total
INTO OUTFILE '/tmp/pedidos.csv'
FIELDS TERMINATED BY ','
FROM pedidos;

-- PostgreSQL: Exportación a CSV con formato ISO
COPY (
    SELECT 
        id_pedido,
        cliente_id,
        TO_CHAR(fecha_pedido, 'YYYY-MM-DD') AS fecha,
        total
    FROM pedidos
) TO '/tmp/pedidos.csv' WITH CSV HEADER;

Formateo para interfaces de usuario

Para presentar fechas en interfaces de usuario, a menudo necesitamos formatos más amigables:

-- MySQL: Formato amigable para UI
SELECT 
    id_evento,
    nombre_evento,
    CASE 
        WHEN DATE(fecha_evento) = CURRENT_DATE() THEN 'Hoy'
        WHEN DATE(fecha_evento) = DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) THEN 'Mañana'
        WHEN DATE(fecha_evento) BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY) 
            THEN DATE_FORMAT(fecha_evento, '%W')
        ELSE DATE_FORMAT(fecha_evento, '%d/%m/%Y')
    END AS cuando,
    DATE_FORMAT(fecha_evento, '%H:%i') AS hora
FROM eventos
WHERE fecha_evento >= CURRENT_DATE()
ORDER BY fecha_evento;

-- PostgreSQL: Formato amigable para UI
SELECT 
    id_evento,
    nombre_evento,
    CASE 
        WHEN fecha_evento::DATE = CURRENT_DATE THEN 'Hoy'
        WHEN fecha_evento::DATE = CURRENT_DATE + INTERVAL '1 day' THEN 'Mañana'
        WHEN fecha_evento::DATE BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days' 
            THEN TO_CHAR(fecha_evento, 'Day')
        ELSE TO_CHAR(fecha_evento, 'DD/MM/YYYY')
    END AS cuando,
    TO_CHAR(fecha_evento, 'HH24:MI') AS hora
FROM eventos
WHERE fecha_evento >= CURRENT_DATE
ORDER BY fecha_evento;

Diferencias clave entre MySQL y PostgreSQL

Es importante tener en cuenta algunas diferencias fundamentales:

• Nombres de funciones: MySQL usa DATE_FORMAT() mientras que PostgreSQL usa TO_CHAR().
• Especificadores de formato: MySQL usa %Y, %m, etc., mientras que PostgreSQL usa YYYY, MM, etc.
• Conversión de tipos: PostgreSQL ofrece el operador :: para conversiones de tipo, mientras que MySQL usa CAST() o conversiones implícitas.
• Localización: PostgreSQL tiene un soporte más robusto para diferentes locales y formatos internacionales.

Mejores prácticas para el formateo de fechas

• Almacenamiento vs. presentación: Almacena siempre las fechas en formatos nativos (DATE, TIMESTAMP) y aplica el formateo solo al presentar los datos.
• Consistencia: Mantén un formato consistente en toda tu aplicación para evitar confusiones.
• Internacionalización: Considera las diferencias culturales en la presentación de fechas (DD/MM/YYYY vs MM/DD/YYYY).
• Formato ISO para intercambio: Usa formatos ISO 8601 cuando intercambies datos entre sistemas.
• Zonas horarias: Especifica siempre la zona horaria cuando sea relevante, especialmente en aplicaciones globales.

El dominio de las funciones de formateo de fechas y horas te permitirá presentar información temporal de manera clara y adecuada para tus usuarios, facilitando la interpretación de los datos y mejorando la experiencia de usuario en tus aplicaciones.